get range and copy range after filtering

[smiler44]

For excel 2007 and 2010

I want to filter a spread sheet and copy the data that is visible.

I don't know the range of the visible cells, how do I get the range of the visible cells?

I think if I just copy visible cells, it copies all blank cells that are outside of my filtered range as well.

thanks
smiler44

 

[boerbende]

If you record a macro in Excel, do whatever you describe here, stop the macro recording and look at the macrocode.
Usually this code is very usefull

 

[smiler44]

borebende,
thanks for the reply. Recording a macro alas will always get the exact range I select but may range will vary. I need to work out the range.

mmm let me go try something

smiler44

 

[smiler44]

how reliable would this code be? this seemed to work and I'm surprised
so I do ask if this would be reliable on a filtered column

 Sub Macro1()
Dim lst As String
Dim lr As String
ActiveSheet.Range("a2").Select  ' row to start
If ActiveCell.Offset(1, 0) <> "" Then
    Selection.End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
lst = ActiveCell.Address ' cell address of first blank cell
 lr = Right(lst, Len(lst) - 3) ' just the number of the first blank cell
lr = lr - 1 ' first blank cell - 1 = last used cell
MsgBox lr
End Sub

smiler44

 

[Brianwarnock]

If you just want the last row then, see below and yes the copy just copies the visible cells.

A simple example would be

Dim lr As Long
lr = UsedRange.Rows.Count
Range("a2:a" & lr).Copy
Paste Sheet2.Range("A1")
Application.CutCopyMode = False

Brian

 

[smiler44]

thanks for the replies I think this is solved
smiler44