VBA code to delete certain rows (until the end) of a table

[Mr2017]

Hi

I've been asked to write code which deletes everything from row 9 downwards in a table.

The table goes from row 7 (which has the titles) and row 8 has some formulas which should be retained.

I'd written this code, which selects a cell in the table, then highlights the region.

Range("D7").CurrentRegion.select

However, I want to offset the region by two rows, so wrote this:

Range("D7").CurrentRegion.Offset(-2,0).Select

However, it won't let me offset the current region by 2 rows (from row 7 to row 9).

Does anyone know how to do this?

I'd then want to delete the new range.

Thanks in advance.

 

[Fluff]

Try

Range("D7").CurrentRegion.Offset(2,0).Select

 

[Joe4]

Just note that your Offset will move the whole range down two rows. So, if you original Current Region ended on row 14, it will move down to row 16.
If there isn't anything under that table, it is probably not a big deal (just selecting/deleting blank rows). However, if you may have more data after a single blank row, it might select/delete stuff in row 16 that you do not want selected or deleted!

If that is the case, you can use Resize to shrink the size of the range by the same number of rows you are moving it down (so it ends in row 14 again).
That code would look something like this:

    Dim myRange As Range
    Set myRange = Range("D7").CurrentRegion
    myRange.Offset(2, 0).Resize(myRange.Rows.Count - 2).Select

 

[Mr2017]

Thanks Fluff!

Ever so helpful!!

Do you know how I can delete that range after it's been selected?

I'd like to do it in a two step process, so instead of just doing



Range("D7").CurrentRegion.Offset(2, 0).delete

I'd like to select it in the way you have, then delete the selected area.

Thanks in advance!

 

[Mr2017]

Thanks for the tip, Joe4 - that's much appreciated!

I won't need to worry about any of the rows below row 8, as the spreadsheet will be populated with new data.

But this is a good tip, in case I need to refer to it in future.

 

[Fluff]

Like this

Range("D7").CurrentRegion.Offset(2,0).Select
Selection.Delete

But you need to take into account what @Joe4 said in post#3

 

[Mr2017]

Ok, I will.

Thanks again!!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Mr2017]

Woo hoo!!

I used what both you and Joe4 said.

I initially got a run time error without resizing, but amended the code with Joe's resize tip and it works like clockwork!

If I wasn't in an office right now, I'd get up and dance!!

Thank you both!!

 

[Mr2017]

I just wanted to say thanks for this tip, Joe, in case you don't see my note of gratitude at the bottom of the thread.

I needed to use it now.

Thanks again! It was very useful!