Code to delete rows after selected cell

[proto]

I've googled, read, searched forum, and etc. without any luck for what I'm trying to do.

I've got a sheet that I build from a weekly sheet. I can have 30 to 60 rows, but I always have 10 to 20 rows left over with data in column C, none in A, my last code is "Range("A1").End(xlDown).Offset(1, 0).Select" which takes me to the last cell in column A. Does anyone know of a code that I can then delete say 20 rows after that? I tried one code, but it wanted to delete everything below the cell selected which ran & ran.

"-" are blanks. Range moves the selected cell to A4. I want to delete row selected + 19 more.

-|A|B|C|D
1|Y|2|9|J|
2|R|1|8|N|
3|G|4|7|N|
4|-|-|L|-|
5|-|-|L|-|

I know I can do this manually very easily, but why mess with that if I can figure this out?

Thanks in advance for any help I may receive!!!

 

[VoG]

Try

Range("A1").End(xlDown).Offset(1).Resize(20).EntireRow.Delete

 

[proto]

It does delete 20 rows, but it goes down and then deletes 20 rows back up. So if there are 40 rows, it goes to the bottom and removes rows 40 - 21.

 

[VoG]

Maybe it should be

Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(20).EntireRow.Delete

 

[proto]

No, still doesn't work. Not sure if it's because Column A has Conditional Formatting or what. I guess this was another reason I was trying to delete from the last row selected and then down.

 

[VoG]

I suspect that your blank cells aren't actually blank but contain spaces.

Try clicking in the first "blank" cell and running

ActiveCell.Resize(20).EntireRow.Delete

 

[proto]

Also, the deletion starts where the C.Formatting ends, then deletes up from that point.

 

[proto]

Still need to test it with more or less data, but here's how I got it to work.

I used your first suggestion:
Range("A1").End(xlDown).Offset(1).Resize(20).EntireRow.Delete
to a new macro button. If I click it once, it'll delete as already stated, but if I click it twice it will delete the remaining cells. I'll play around with it some more.

 

[proto]

Now went back and added

Range("A1").End(xlDown).Offset(1).Resize(20).EntireRow.Delete
Range("A1").End(xlDown).Offset(1).Resize(20).EntireRow.Delete

as my last two statements of my original macro and it works. Odd, but it works.

Thanks!!!

 

[proto]

Added and removed rows for test and works with no problems. So thanks again.