Finding the end of a worksheet

[Dan Wilson]

Good day. I am running Excel out of Office365 (updated) on Windows 10 Home. I have a workbook that creates a worksheet to list almost 5,000 songs in my Music folder. I have added some subroutines to the macro to format the new worksheet after the data has been installed. I have accomplished all my goals except one. As I am constantly adding songs to my Music folder. I need a way to determine the last used Row in the worksheet. In the macro is a section copied below that puts Borders around the existing data. Currently, I have to modify the range in the macro every time I create a new database with added songs. Is there a way to modify the Range in the sub below to automatically find the last row with data? Thanks for any help on this.
Dan Wilson...

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A2:H4560").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin

 

[Fluff]

Will there be any column that always has data in every cell?
If so which?

 

[Dan Wilson]

Will there be any column that always has data in every cell?
If so which?

Good day Fluff. I meant to include that, but did not. Column A will have data from A1 to the last row of data.
Thanks, Dan Wilson...

 

[Fluff]

How about

With Range("A2:H" & .Range("A" & Rows.Count).End(xlUp).Row)
   .Borders(xlDiagonalDown).LineStyle = xlNone
   .Borders(xlDiagonalUp).LineStyle = xlNone
   With .Borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .ColorIndex = xlAutomatic
      .TintAndShade = 0
      .Weight = xlThin
   End With
End With

 

[6StringJazzer]

You could also use conditional formatting to add the borders instead of using code. You can have a rule that says

=A1<>""

Applies to: A:H

and the formatting is the border you want.

 

[Dan Wilson]

How about

With Range("A2:H" & .Range("A" & Rows.Count).End(xlUp).Row)
   .Borders(xlDiagonalDown).LineStyle = xlNone
   .Borders(xlDiagonalUp).LineStyle = xlNone
   With .Borders(xlEdgeLeft)
      .LineStyle = xlContinuous
      .ColorIndex = xlAutomatic
      .TintAndShade = 0
      .Weight = xlThin
   End With
End With

Good day Fluff. I entered the code and when I run the macro, the Debugger pops up with the word ".Range" highlighted and an error statement of "Compile Error - Invalid or unqualified reference". I checked that I entered everything correctly. I have copied what I entered below.

With Range("A2:H" & .Range("A" & Rows.Count).End(x1Up).Row)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End With

Thanks, Dan Wilson...

 

[Rick Rothstein]

Try removing the dot in front of ".Range".

 

[6StringJazzer]

(incorrect post deleted)

 

[Fluff]

Along with what Rick has said you also have x1Up which should be xlUp (lowercase L not number one)

 

[Dan Wilson]

You could also use conditional formatting to add the borders instead of using code. You can have a rule that says

=A1<>""

Applies to: A:H

and the formatting is the border you want.

Good day 6StringJazzer. Thank you for the suggestion. That would make the border the same for all 5,000 rows. I will experiment with it later.
Thanks, Dan Wilson...