Format

[Ody]

Good day all,

I'm trying to right some code that will format the data I have starting in row 8 and going to the last row of data in my table (which is always different). I get the VB for formatting, I'm just stuck on how to write the dynamic part of the code, if that makes sense.

Appreciate any assistance.

Thanks!

 

[Desu Nota from Columbus]

is there anything below you table or just blank cells?

 

[Ody]

Below the table is just blank cells. The range is A7:R7 to __?___.

 

[MrKowz]

Try:

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A7:R" & LR).Select

 

[Desu Nota from Columbus]

Above is the legit answer, I have a backwoods way of selecting the first open cell for future reference.

If I have to refer to next open cell in a column, I use this:

Columns("A:A").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select

selects the very last cell in the column and then goes up to the first cell with data in it, then moves down one...if that makes sense.

 

[MrKowz]

Above is the legit answer, I have a backwoods way of selecting the first open cell for future reference.

If I have to refer to next open cell in a column, I use this:

Columns("A:A").Select
    Range("A65536").End(xlUp).Offset(1, 0).Select

selects the very last cell in the column and then goes up to the first cell with data in it, then moves down one...if that makes sense.

I would highly recommend changing that code to:

Range("A" & Rows.Count).End(xlUp).Offset(1,0).Select

By using Rows.Count instead of hardcoding the last cell, it gives you more universal compatability if you were to import the code into 2007 or 2010 (since they have more rows than 2003 and older).

 

[Ody]

Perfect, thank you both!

 

[Desu Nota from Columbus]

I would highly recommend changing that code to:

Range("A" & Rows.Count).End(xlUp).Offset(1,0).Select

By using Rows.Count instead of hardcoding the last cell, it gives you more universal compatability if you were to import the code into 2007 or 2010 (since they have more rows than 2003 and older).

Thanks for the tip!