macro help needed for selecting the last row in a range

[dankrems]

Hi,

I've used this site many times before (and love it!) but never posted because I could always find the answer to my question in another thread. I've now encountered a problem I can't seem to figure out on my own.

I'm writing a macro for a "revenue by account" spreadsheet that will have a variable number of rows. The last row of the spreadsheet contains a grand total of each of the columns in hard code. I want the macro to replace these hard coded numbers with a sum formula that sums each column from row 7 down through the 2nd to final row. I also want to alter the formatting of some of the cells in the row.

I recorded a macro using keystrokes and I am having some problems because the macro I have recorded has a hard coded row number.

The macro is working successfully to identify the last row, now what I need is a little help with what command I should use to indicate "make changes to the last row or certain cells in the last row" rather than "make changes to row 486 or certain cells in row 486"

Here is an excerpt of some of the code:

Range("S7").Select
Selection.End(xlDown).Select
Range("S7").Select
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
ActiveCell.FormulaR1C1 = "Totals"
Rows("486:486").Select
Selection.Font.Bold = True
Range("C486").Select
Selection.Style = "Currency"
Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""??_);_(@_)"
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

[/code]

 

[sweater_vests_rock]

hi dankrems. glad that you've found the board so helpful.

indentifying the last row in a column can be problematic at times, but one solution which usually works is below.

cheers. ben.

Sub FindLastRow()
    Dim LastCell As Range
    Dim StartRow As Long, MyColumn As Integer

    StartRow = 1 '<-- Change this to the appropriate row
    MyColumn = Columns("A").Column '<-- Change this to the appropriate column
    
    Set LastCell = Range(Cells(StartRow, MyColumn), _
        Cells(ActiveSheet.Rows.Count, MyColumn).End(xlUp))
    
    MsgBox "The last row in the range is " & LastCell.Row & "."
    
End Sub