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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
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)
Good day Fluff. Good catch! I would never had figured that one out. They look so much alike. The macro now runs error free, but no formatting is done to the worksheet after Row 1. Row 1 Columns A thru H contain column headers. Column A will contain song titles all the way from Row 2 through the last Row used. Dependent on the data in the file properties of each song, Columns B thru H may or may not contain data. Row 1 will have solid lines on the outside of each column and dotted lines separating the columns. That part of the format macro works. I purposely cleared all the formatting of the entire worksheet and ran a test of the formatting macro. Row 1 was formatted correctly, but the rest of the worksheet had no formatting done. I appreciate your diligence with this one and hope that there is an easy fix. If not, then I will continue to alter the previous formatting macro to adjust the number the last used Row.
Thank you, Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Are you trying to put a thin vertical border between each column, or just on the left hand side of column A?
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you trying to put a thin vertical border between each column, or just on the left hand side of column A?
Good day Fluff. To make things as easy as possible, If I can get a solid line border around A1 to Hxxxx (Hxxx being the last row to contain data) and then dashed lines surrounding each cell inside that same area, I will be happy. If not, then I will handle it by editing the macro each time a new song is added to the music folder.
Thank you for sticking with this. I appreciate the extra help.
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub DanWilson()
   With Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row)
      .Borders.Weight = xlHairline
      .BorderAround , xlThin
   End With
End Sub
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub DanWilson()
   With Range("A1:H" & Range("A" & Rows.Count).End(xlUp).Row)
      .Borders.Weight = xlHairline
      .BorderAround , xlThin
   End With
End Sub
Good day Fluff. I tried your suggestion. The macro complains about about the code ".BorderAround , xlThin". I tried the following, but still get an error.

.BordersAround = xlThin
.Borders.Around = xlThin
.Around = xlThin
.Around , xlThin

Perhaps Around is the problem as I did not encounter this in any of the macros that I created using Record Macro and mouse selections. Thank you for your pesistance.
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
In what way did it "complain"?
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
In what way did it "complain"?
Good day Fluff. When I run the original version that you sent to me I get

Run-time error '438'
Object doesn't support this property or method

When I click on Debug, it then shows highlighted in Yellow

.BordersAround , xlThin

I get the same error when I tried the other combinations listed previously. Thank you for helping.
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
Its is always best to copy the code & paste it into the editor, rather than retyping, that way you won't make any typos.
It should be BorderAround (no s)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,133
Messages
5,628,896
Members
416,352
Latest member
Lunox01

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