VBA Macro needed to delete X amount of rows below the Nth row

[jsirish]

Thanks in advance for the help.
I have a set of data with a dynamic number or rows. Could be 10, could be 1000. I want to find the last row of data before any spaces, and delete the next 12 rows. Any help would be great!

I added a sample file of what I'm trying to get accomplished. For the file attached, I want the macro to find the cell after 78627(A:20) and delete the next 12 rows. The rows are dynamic, so the formula needs to be able to recognize the first blank cell whether it happens to be A:5 or A:1000.

Thanks for the help, I know this is a tough one.

[HaHoBe]

Hi, jsirish,

maybe try soimethin glike this on a copy of the workbook:

Please Login or Register  to view this content.

Code goes into a standard module and may be called either by placing the cursor into the procedure and press F5 (run entire procedure) or F8 (single step through).

Ciao,
Holger

[jsirish]

That Gives me a run-time error 1004. I like the idea with the resize function, but it must be missing something?

[JieJenn]

Your statement is a little bit confusion, but try

Please Login or Register  to view this content.

[jsirish]

That seems to delete the rows, however it deletes 12 rows below where the current active cell is. How do I reference the first blank cell after the count function?

[HaHoBe]

Hi, jsirish,

code was tested on your sample workbook and worked fine there. 1004 may i.e appear if the whole range is filled up to the last cell in the column (CTRL + Down) which wasnīt hte case in the example.

Ciao,
Holger

[jsirish]

It seems to be performing the delete function before it gets down to the bottom of the column?

[HaHoBe]

Hi, jsirish,

It seems...

Hmm - code doesnīt seem not to do what itīs supposed to do (at least for me in the example ).

You delivered a sample, starting with a block of data in Range("A4"). The code starts from that cell, goes down to the last filled cell without blanks in Column A, then goes one row below and builds a range of 12 cells in Column A where the rows are deleted.

Starting from any cell iside the block except for the last cell will have the same result. Different results will be achieved if you start in Range("A19") where no run time error would occur but rows from Row 22 would be deleted.

Form your information Row 32 in Sheet 1 will not be deleted as that would be 13 rows below the given number which happens to be in A19.

HTH
Holger

[jsirish]

I Attached a new file to the first post that is in the exact format of the file I'm trying to work with. Possibly the other file was too different. Can anyone help??

Again, what I'm trying to do is delete everything after row 19 on this spreadsheet. However, the rows will be dynamic so in one case it might be deleting everything after row 5, and in others it will be deleting everything after row 1000.

Thanks in advance!!

[HaHoBe]

Hi, jsirish,

a slightly different approach for now:

Please Login or Register  to view this content.

Ciao,
Holger