Excel Macros for Worksheets

Sample macros for worksheet tasks, from Excel MVP Rick Rothstein. Fix numbers with trailing number signs, find the last row with data, select the sheet's used range, or select from the first cell with data.

last row number

Fix Numbers With Trailing Minus Signs

Find Last Row With Data

Select Used Range on Worksheet

Select from First Cell With Data

About Rick Rothstein

Fix Numbers With Trailing Minus Signs

If you import data into Excel, some of the numbers might have a minus sign at the end of the number, instead of before the number. That data will be treated as text, instead of numbers, and won't show the correct total, if you use the SUM function.

trailing minus sign

You can fix those trailing minus signs manually, but if this is a frequent problem, use the following macro to fix all the trailing numbers on the active worksheet.

The code helps ensure that you don't accidentally process any text cells containing delimiters from a previous use of the TextToColumns feature.

It also protects against there being an empty column within the UsedRange.

Sub FixTrailingNumbers()
  Dim Col As Range
  On Error Resume Next
  For Each Col In ActiveSheet.UsedRange.Columns
    Col.TextToColumns Col, xlFixedWidth, _
       FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
  Next
End Sub

Find Last Row With Data

When you're working with Excel VBA, you might want find the last row with data on a worksheet, so you can paste new data in the row below that. Some methods, like the line of code shown below, can cause unexpected results in there is an Excel Table on the worksheet..

LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

To prevent problems, use the following line of code in your existing macro, to reliably find the last row.

LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
   SearchDirection:=xlPrevious, LookIn:=xlValues).Row

Note: This code ignores cells with formulas that are displaying the empty string. If you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.

Use the LastRow Code

As an example, the LastRow code is in this short macro that displays a message box.

Sub GetLastRowWithData()
  Dim LastRow As Long

  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
     SearchDirection:=xlPrevious, LookIn:=xlValues).Row

  MsgBox LastRow

End Sub

When the GetLastRowWithData macro runs, a message box displays the correct number of the last row with data on the active worksheet.

last row number

Select Used Range on Worksheet

To select the used range a worksheet, you can use the following sequence of keyboard shortcuts:

Press Ctrl + Home, to go to cell A1
Press Ctrl + Shift + End

All cells between cell A1 and the last used cell are selected. Sometimes though, the selected range is bigger than expected. It might include cells that are empty, but formatted. Or the selected range could include cells where previous values have been deleted.

To select the actual used range, you can use this macro instead:

Sub PickedActualUsedRange()
  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
End Sub

Select from First Cell With Data

The previous macro selects from cell A1 to the last cell that contains data. If your worksheet's data does not start in cell A1, and you really want the actual used range on the worksheet, then you can use the following macro.

Sub SelectActualUsedRange()
  Dim FirstCell As Range, LastCell As Range
  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, _
      SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, LookIn:=xlValues).Column)
  Range(FirstCell, LastCell).Select
End Sub

About Rick Rothstein

I graduated college as a Math Major and then ended up working for some 32+ years as a Civil Engineer (Road Design) for the New Jersey Department of Transportation. I originally started programming in BASIC on a Texas Instruments TI-99/4 as a hobby.

Eventually I formed a company (FFF Software) with two other gentleman and we sold a couple of TI-BASIC programs commercially for a couple of years. I also sold several articles on programming the TI-99/4 to COMPUTE! Magazine. Once TI went out of the home computer market, I purchased a Radio Shack Model 100 (first truly portable computer) and programmed on it (again, as a hobby) for a couple of years. Again, I sold several articles to a couple of magazines that covered this particular computer.

Before buying my first IBM compatible PC, I owned a Commodore 128 and Atari 520. Once I discovered Visual Basic, I was completely hooked. I continued writing articles on and off across the years, but now exclusively for Visual Basic; and, of course, I used Visual Basic in my workplace to make my job, and the job of my co-workers, easier.

About a year or so before I retired, I decided to start helping others with their programming needs by volunteering to answer questions on various Visual Basic newsgroups. In 2007 or so, I branched out and began answering questions on Excel newsgroups as well. I have been retired for a few years now but my Excel (and now to a lesser extent VB) newsgroup volunteer efforts are still continuing to this very day.

Rick Rothstein

Rick Rothstein

Excel Macros for Worksheets

Fix Numbers With Trailing Minus Signs

Find Last Row With Data

Use the LastRow Code

Select Used Range on Worksheet

Select from First Cell With Data

Related Excel VBA Tutorials

About Rick Rothstein