Finding the end of a worksheet

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Will there be any column that always has data in every cell?
If so which?
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

Well-known Member
Joined
Jan 27, 2010
Messages
826
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Excel Formula:
=A1<>""
Applies to: A:H

and the formatting is the border you want.
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,896
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Along with what Rick has said you also have x1Up which should be xlUp (lowercase L not number one)
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
You could also use conditional formatting to add the borders instead of using code. You can have a rule that says

Excel Formula:
=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...
 

Watch MrExcel Video

Forum statistics

Threads
1,128,138
Messages
5,628,919
Members
416,353
Latest member
Nenza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top