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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,852
Office Version
  1. 2010
Platform
  1. Windows
Its is always best to copy the code & paste it into the editor, rather than retyping...
Just to follow up on Fluff's comment, when you see code posted in a code window within a message on this forum, look at that code window's title bar for an icon that looks like (to me) two overlapping sheets of paper... click that icon and all the code in the code window will be copied to your clipboard making it easy for you to paste it into your Excel code window without errors.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
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)
Good day Fluff. BINGO! I missed that one too. My eyes were just recently checked too. Not only is there a border around the entire data area, but dashed lines separating the cells as well. I truly appreciate your patience and persistence on this one. I will mark at as closed.
Dan Wilson...
 

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
Just to follow up on Fluff's comment, when you see code posted in a code window within a message on this forum, look at that code window's title bar for an icon that looks like (to me) two overlapping sheets of paper... click that icon and all the code in the code window will be copied to your clipboard making it easy for you to paste it into your Excel code window without errors.
Good day Rick Rothstein. Thank you for the shortcut to copying and pasting code. It would have saved Fluff extra time on two different posts.
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,208
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,893
Messages
5,621,486
Members
415,844
Latest member
Reda Fouad Ramzy

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