JimS
09-10-2012, 08:03 AM
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
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