Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Filtered records only when AutoFilter is on
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Filtered records only when AutoFilter is on
I'm probably not the person to be answering this, but did you mean you want the rows that are hidden in the filter to be or to not be selected?
If maxrow is say 3, so that your range to select would be B12:F14 (maxrow+11), and say rows 12 and 13 are hidden because of filtering, you want only B14:F14 to be selected, or did you want B12:F14 to be selected even tho they are filtered? As is, this macro would select B12:F14 even if rows 12 and 13 are filtered. If you would only want B14:F14 to be selected, your Range line should be: Range("B" & currentrow & ":" & "F" & maxrow + 11).SpecialCells(xlCellTypeVisible).Select HTH "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Filtered records only when AutoFilter is on
One more way:
Option Explicit Sub RefreshModel() Dim rng As Range With ActiveSheet.AutoFilter.Range If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count 1 Then Set rng = .Offset(1, 0).Resize(.Rows.Count - 1) _ .SpecialCells(xlCellTypeVisible) Else MsgBox "no Visible cells in filter!" Exit Sub End If End With rng.Select End Sub This one looks to see how many visible cells are in the first column after the filter. If it's more than one (the header row is always counted), then it comes down one row and resizes (by -1) to ignore the header row. Owen Vickers wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete the filtered records | Excel Discussion (Misc queries) | |||
How to obtain the number of filtered records in Autofilter | Excel Worksheet Functions | |||
how do I display filtered records found | Excel Discussion (Misc queries) | |||
returning value from filtered records | Excel Discussion (Misc queries) | |||
how do I see more than 1000 records filtered | Excel Discussion (Misc queries) |