macro help needed for selecting the last row in a range

dankrems

New Member
Joined
Dec 28, 2005
Messages
1
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]
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,669
Members
412,481
Latest member
nhantam
Top