Finding the last instance of a word and working relative to that cell

[titaniumbean]

Hi, I think this is a very basic question but i'm an utter noob at vba.
I've just started recording some macros to have a look at basic syntax and functions.

I have data brought in from another system which is formatted for printing. So after every x amount of lines it breaks onto another page with the heading information and some page information on each. I want to automate removing all of these with a macro.

I want to search column A for instances of the word 'xyz' then I want to delete the blank row above this word, and the 6 (not all blank) rows below it.


Ideally I then want it to loop and find the (next last) version of the word 'xyz' and do the same until it gets back to the top and finds no instances of the word.


I don't want to just put put Row(8).select selection.delete because I wont know how many pages will have been inputted so I need the script to loop until all instances are cleared rather than assuming i'll know where the instances of 'xyz' are always going to appear.

Does this make sense and if so which direction do I need to be pointed in?

Would it be complicating it too much to want to be able to have some instances of 'xyz' at the top of the page that are essentially in frozen panes and so are not touched.

Thanks in advance.

 

[MickG]

Hi, Try this:- It should leave row(1) with original Data (xyz).
The code loops from Bottom to top, so if you want to leave a number of rows intact change the "2" in (Last To 2) to whatever.
This Code removes the row with "xyz", the row above, and 6 riows below.

 Sub RemRws()
Dim Last As Long, xyz As Long
Last = Range("A" & Rows.Count).End(xlUp).Row
For xyz = Last To 2 Step -1
    If Cells(xyz, "A") = "xyz" Then
        Rows(xyz).Offset(-1).Resize(8).EntireRow.Delete
    End If
Next xyz
End Sub

Mick

 

[titaniumbean]

Hi, Try this:- It should leave row(1) with original Data (xyz).
The code loops from Bottom to top, so if you want to leave a number of rows intact change the "2" in (Last To 2) to whatever.
This Code removes the row with "xyz", the row above, and 6 riows below.

 Sub RemRws()
Dim Last As Long, xyz As Long
Last = Range("A" & Rows.Count).End(xlUp).Row
For xyz = Last To 2 Step -1
    If Cells(xyz, "A") = "xyz" Then
        Rows(xyz).Offset(-1).Resize(8).EntireRow.Delete
    End If
Next xyz
End Sub

Mick

Thanks very much for the response.

Could you essentially note it aswell just so I can learn from it.

"Last = Range("A" & Rows.Count).End(xlUp).Row" -

Is this saying that the variable we have assigned as Long works down column A to the final field with data in then basically just skips back up one cell?

" Rows(xyz).Offset(-1).Resize(8).EntireRow.Delete"

This segment takes the row in which xyz has been found and the -1 takes the row above, and then the 8 allows for going to the 1 below xyz and the 6 it wants to delete?

The step -1 bit obviously means it loops upwards one row at a time right?

Sorry if this is horrifically basic but the only way I can seem to learn code is when there is pseudocode notes with it to help me follow it through.

Thanks again.



edit-

also; if I want to do exactly the same thing but if I want to find the print stamp (ie its multiple words not one word like in the XYZ example) how can I search for the start of the string rather than an exact match for the whole thing?

 

[MickG]

Hi, No Problem :- Hope this helps !!

 Sub RemRws()
Dim Last As Long, xyz As Long
' dim as long, as integer only goes to approx 32k and your data could go to (65K)

'This is pretty standard code for finding the Last row
'The code looks to the last Row (65536)(XL2007 has a lot more) then counts Up
'this is to avoid Blank Rows in Data, which would Happen if you counted down
Last = Range("A" & Rows.Count).End(xlUp).Row

'When Removing Rows if you don't go from the Bottom Up, you run
'in to problems because if you have rows together that meet the
'Criteris for deletion, you end up deleting the first row
'then the next row becomes the current row and you end up missing it.

For xyz = Last To 2 Step -1
    If Cells(xyz, "A") = "xyz" Then
        'This bit is just a way of Capturing the 8 rows.
        'Move up one, resize the range to 8
        Rows(xyz).Offset(-1).Resize(8).EntireRow.Delete
    End If
Next xyz
End Sub

With regard to you "Edit" you could Use something like

If Instr(Cells(xyz,1), MylettersInString) then  
do Somthing here
end if

Regards Mick

 

[titaniumbean]

Hi, No Problem :- Hope this helps !!

 Sub RemRws()
Dim Last As Long, xyz As Long
' dim as long, as integer only goes to approx 32k and your data could go to (65K)

'This is pretty standard code for finding the Last row
'The code looks to the last Row (65536)(XL2007 has a lot more) then counts Up
'this is to avoid Blank Rows in Data, which would Happen if you counted down
Last = Range("A" & Rows.Count).End(xlUp).Row

'When Removing Rows if you don't go from the Bottom Up, you run
'in to problems because if you have rows together that meet the
'Criteris for deletion, you end up deleting the first row
'then the next row becomes the current row and you end up missing it.

For xyz = Last To 2 Step -1
    If Cells(xyz, "A") = "xyz" Then
        'This bit is just a way of Capturing the 8 rows.
        'Move up one, resize the range to 8
        Rows(xyz).Offset(-1).Resize(8).EntireRow.Delete
    End If
Next xyz
End Sub

With regard to you "Edit" you could Use something like

If Instr(Cells(xyz,1), MylettersInString) then  
do Somthing here
end if

Regards Mick

Thank you very much Sir ray:

Both for the quick responses and the detailed notes.