varying table length in a MACRO

[tlwhite]

I would like to perform a macro on multiple spreadsheets with varying numbers
of rows. When recording the macro, I used the keystrokes to go to the end of
the spreadsheet; however, when I apply the macro to other spreadsheets, it
uses a fixed row area. How can I get this to go to the end of the
spreadsheet?

tlwhite

Range("A2").Select
ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, Cells(1)).Select
Range("B1:M2028").Select
Range("M2028").Activate

 

[Mike H]

Hi,

The macro below will loop through the named sheets and find the last row in
column A of each sheet.

Sub Sonic()
Dim V As Variant
Dim S As String
Dim Sh As Worksheet
Dim LastRow As Long
S = "Sheet1,Sheet2,Sheet3"
V = Split(S, ",")
For Each Sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(CStr(Sh.Name), V, 0)) Then

'This line finds the last row on column A
LastRow = sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row


End If
Next Sh

End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Don Guillett]

Sub lastrowineachsht()
'On Error Resume Next
For i = 1 To Sheets.Count
With Sheets(i)
'MsgBox Sheets(i).Name
lr = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
, , , xlByRows, xlPrevious).Row
'MsgBox lr
..Range("B1:M" & lr).Interior.ColorIndex = 16 'Select
End With
Next i
End Sub