Solved: Delete a six-row block which occurs every n rows

[klinden]

Hi all,

I export a spreadsheet from a SAP report. A six-row page header occurs after every 52th row in the spreadsheet (i.e. rows 1-6 = header, rows 7-58 = data, rows 59-64 = header, and so on). All headers are identical.

I'd like to delete all but the first instance of this page header. The report can easily be thousands of rows, so doing this manually every time is getting rather tedious. I've recorded a macro for this, deleting every header manually, and looked at the code in the VBE but I guess I should be using some kind of loop for this task? That's where it gets too challenging for a "noob" like myself. :banghead:

How would I go about doing this using VBA?

Thanks!

[mancubus]

welcome to the forum.

try this:
Sub del_repeating_header_rows()
'activate the worksheet with imported data before running macro

Dim LastRow As Long, i As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

LastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = LastRow To 7 Step -1
If (i Mod 58 = 6) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 5) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 4) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 3) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 2) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 1) Then Rows(i).EntireRow.Delete
Next i

End Sub

[klinden]

welcome to the forum.

try this:
Sub del_repeating_header_rows()
'activate the worksheet with imported data before running macro

Dim LastRow As Long, i As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

LastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = LastRow To 7 Step -1
If (i Mod 58 = 6) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 5) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 4) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 3) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 2) Then Rows(i).EntireRow.Delete
If (i Mod 58 = 1) Then Rows(i).EntireRow.Delete
Next i

End Sub


Thanks! Worked like a charm. :thumb

[mancubus]

you're welcome.


another way may be applying a filter on row 6.
filter rows with header strings(any column).
select the rows below row 6.
right click. then delete.

[mancubus]

i wonder why i wrote six separate lines :dunno
:D


For i = LastRow To 7 Step -1
If (i Mod 58 <= 6) And (i Mod 58 >= 1) Then Rows(i).Delete
Next i

[snb]

or

Sub M_snb()
Set c00 = sheets(1).Cells(59, 1).Resize(6)
For j = 59 To Sheets(1).UsedRange.Rows.Count Step 58
Set c00 = Application.Union(c00, Cells(j, 1).Resize(6))
Next

c00.ClearContents
Sheets(1).SpecialCells(4).EntireRow.Delete
End Sub