Hi -
I am trying to write a macro which will start in one cell which I will
determine (A12 for example), count down 11 rows and select all of the 11
rows. Basically I have some extraneous data which occurs every 34 rowsand
includes 11 rows worth of data - always! I want to have the macro start in a
cell, count down 34 select the 11 rows and delete them, then loop until it
gets to the end of the data. I'm thinking this is probably an Offset of some
kind but just can't seem to make it work.
Thanks for any help you might provide!!
Kelley
Kelley,
Usually deletion occurs in a reverse order (i.e. Step -1 in your For
Next loop); however, you can create a range with Union and then delete
at the end in this case because I'm not sure how big your data set
is. There is some code below wherein I created a number of variables
to try and help with understanding what the program is doing (i.e. I
tried to spell it out). Nonetheless, you should be able to follow
this and feel free to cut out variables if necessary by consolidating
the code. I haven't really tested this, so be sure to run this step-
by-step (F8 repeatedly) to see how the program is behaving. Also,
make sure you save a back up of your data before you start running
this macro because once you delete something, you won't get it back.
(You could comment out the rngDelete.EntireRow.Delete line and place a
debugging line in the same location, such as Debug.Print
rngDelete.Address to print to the Immediate Window (View | Immediate
Window) to see if the right range is being selected for deletion).
Best,
Matthew Herbert
Sub DeleteEveryXRows()
Dim rngAnchor As Range
Dim rngData As Range
Dim rngDelete As Range
Dim rngRows As Range
Dim rngBlock As Range
Dim intOffset As Integer
Dim intDeleteRows As Integer
Dim intTotal As Integer
Dim intCnt As Integer
Dim intLoop As Integer
Set rngAnchor = ActiveSheet.Range("A12")
Set rngData = Range(rngAnchor, rngAnchor.End(xlDown))
intOffset = 34
intDeleteRows = 11
intTotal = intOffset + intDeleteRows
intLoop = rngData.Rows.Count / intTotal
For intCnt = 1 To intLoop
Set rngBlock = rngAnchor.Offset(intTotal * intCnt - 1, 0)
Set rngRows = Range(rngBlock, rngBlock.Offset(-intDeleteRows + 1,
0))
If rngDelete Is Nothing Then
Set rngDelete = rngRows
Else
Set rngDelete = Union(rngDelete, rngRows)
End If
Next intCnt
rngDelete.EntireRow.Delete
End Sub
