PDA

View Full Version : [SOLVED] Macro to delete all rows between two different text stings in column A



RINCONPAUL
05-08-2016, 03:05 PM
As a means to an end, the solution to this post might help remove another problem I have outlined in another post? In column A, I am after a macro to delete all rows (blank or otherwise between two specified strings of text. Namely "TAB:" and "SKY". They will only appear once each in a column but spaced indeterminably:
jon
Param

TAB:
frit

brat
Gun

SKY
sop
net

The sheet after the macro, would appear as:
jon
Param

TAB:
SKY
sop
net

Thanks

Paul_Hossler
05-08-2016, 07:51 PM
Option Explicit
Sub test()
Dim iTABrow As Long, iSKYrow As Long

iTABrow = 0
iSKYrow = 0

On Error Resume Next
iTABrow = Application.WorksheetFunction.Match("TAB:", ActiveSheet.Columns(1), 0) + 1
iSKYrow = Application.WorksheetFunction.Match("SKY", ActiveSheet.Columns(1), 0) - 1
On Error GoTo 0


If iTABrow = 1 Or iSKYrow = -1 Then Exit Sub

ActiveSheet.Rows(iTABrow & ":" & iSKYrow).Delete
End Sub

RINCONPAUL
05-08-2016, 10:58 PM
Just as cream rises to the top, and greatness to the fore, you have once again proved your skills, Paul. Thankyou, case closed.

Paul_Hossler
05-09-2016, 09:26 AM
<blush>

You can use [Thread Tools] on the menu above your #1 to make it [Solved]

RINCONPAUL
05-09-2016, 01:45 PM
Can I add an addendum to this post? Paul, a weird issue has arisen? Code works well if you type the text into a spreadsheet and enact the macro.... BUT....if the text is pasted from a web page query into excel, you get an error with the line, "ActiveSheet.Rows(iTABrow..........Delete, which is what happens in reality. Any solutions?
Cheers

Paul_Hossler
05-09-2016, 03:30 PM
Attach something to look at

Paste the text and save the WB as is

RINCONPAUL
05-09-2016, 03:43 PM
Here's typical worksheet after the web page has been pasted in by a web query. In this instance I want to delete all rows between the occurrence of text "TAB:", 'A98" and "Print This Meeting", 'A266'. (Cell references always differ).

I think many of my problems occur, in that I make the example too simplified to the real world case?:banghead:

Paul_Hossler
05-09-2016, 04:32 PM
1. In that file in A98 there is TAB: followed by 4 spaces

2. In column A there are 24 cells containing 'Sky', 15 with 'Sky' in the first position

3. 'Print This Meeting' or 'SKY' ??? The macro below search for 'Print This Meeting'



Option Explicit

Sub test()
Dim iTABrow As Long, iMTGrow As Long

iTABrow = 0
iMTGrow = 0

On Error Resume Next

iTABrow = ActiveSheet.Columns(1).Find(What:="TAB:", After:=ActiveSheet.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Row + 1

iMTGrow = ActiveSheet.Columns(1).Find(What:="Print This Meeting", After:=ActiveSheet.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Row - 1

On Error GoTo 0

If iTABrow = 1 Or iMTGrow = -1 Then Exit Sub

ActiveSheet.Rows(iTABrow & ":" & iMTGrow).Delete
End Sub

RINCONPAUL
05-09-2016, 05:01 PM
"Exhalted", the best word to describe someone who surpasses past achievements, that being you Sir!:trophy: