Finding the end of a worksheet

Dan Wilson

Well-known Member
Joined
Feb 5, 2006
Messages
504
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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...
 
Upvote 0
Are you trying to put a thin vertical border between each column, or just on the left hand side of column A?
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
In what way did it "complain"?
 
Upvote 0
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...
 
Upvote 0
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)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top