View Full Version : Solved: Delete a six-row block which occurs every n rows
klinden
07-08-2013, 04:50 AM
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
07-08-2013, 05:21 AM
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
07-08-2013, 05:28 AM
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
07-08-2013, 05:46 AM
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
07-10-2013, 12:26 AM
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.