Macro to count down and select specific number of rows

K

Kell2604

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 rows and
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
 
M

Matthew Herbert

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top