Solved: VBA: Unusual LastRow Range

[JimS]

I use the following code that copies the visible cells (as a result of an autofilter by color) but it doesn't like when the lastRow = 2.

The ActiveSheet.Range ends up being A2:A2.

When this happens it selects all the visible cells.

Row 1 (cell-A1) is a Header Row.

Any ideas?

Thanks...

JimS




Sub copyTrans()

Dim lastRow As Integer

lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row 'Get last row

If lastRow = 1 Then
Sheet2.Select
Range("B2").Value = "No Items were Selected"
Else

Sheet1.Select
ActiveSheet.Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).Copy 'Copy visible

Sheet2.Select

Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True 'Paste them transposed


End If

End Sub

[Aflatoon]

Add a test for lastRow = 2 and then just copy A2. :)

[JimS]

I could do that - Thanks.

Any idea why A2:A2 isn't valid?

[Aflatoon]

If you use SpecialCells on one cell, it works on the entire sheet (I guess because it doesn't really make sense otherwise)

[JimS]

Got it - Thanks again...

[Bob Phillips]

Jim,

You really shouldn't be selecting anything

Sub copyTrans()

Dim lastRow As Long

With Sheet1

lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Get last row

Select Case lastRow

Case 1: Sheet2.Range("B2").Value = "No Items were Selected"

Case 2: .Range("A2").Copy Sheet2.Range("A2").PasteSpecial(Paste:=xlPasteValues)

Case Else
.Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).Copy
Sheet2.Range("A2").PasteSpecial Paste:=xlPasteValues, Transpose:=True
End Select
End With
End Sub

[JimS]

xld,

Thanks for the code.

Very appreciated...

JimS