Selecting Filtered records only when AutoFilter is on
Owen: I do filter, copy, and paste all the time. But I use Paste
SpecialValues Only (and then sometimes will go back and add Columns Widths
and Formats). This method leaves some things behind, I'm sure. But if it
will work for you, here's the code I use to copy a filtered range and paste
to a new sheet.
' Find end of data, start for lists
Range("B65536").End(xlUp).Select
LastRow = ActiveCell.Row
' Select range and copy
Range("A1:U" & LastRow).Select '<<Change A1 to your starting cell
Selection.Copy
' Open Calc_Stats workbook
Set wb2 = Workbooks.Open(strFPath & "Calc_Stats.xls")
' Paste in data
wb2.Activate
Sheets("Sheet1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
HTH
Ed
"Owen Vickers" wrote in message
om...
From an earlier posting on the same subject I found some useful code
that allows me to store the "visible" data/rows in a variable...
thanks to Dave Peterson for that!
The code I used is as follows:
Sub RefreshModel()
Dim rng As Range
Dim maxrow As Integer
Set rng = ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(rng.rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
'rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "0 rows"
Else
maxrow = rng.Cells.Count
End If
ActiveSheet.Select
currentrow = 12
Range("B" & currentrow & ":" & "F" & maxrow + 11).Select
End Sub
I get the correct value in the variable for maxrow but it is not
selecting the correct range!
I want to select the range B12 to F:maxrow ...the number of visible
rows but I do not understand the Resize and Offset methods in the code
above meaning that the selction is not working properly and does not
select all the Filtered rows!
What am I missing or not understanding?
Owen
|