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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.