Hi,
Rather than attempt to re-write your code I thought that a little lesson in
identifying the visible cells will be better then you can adjust your code. I
have tried to keep simple code for the demo. Not necessarily the best way to
write it
Note all the comments in the code. Also a space and underscore at the end of
a line is a line break in an otherwise single line of code.
I use the following line of code because I have never been able to count the
number of visible rows but can count the number of visible cells and the
number of columns in the autofilter range so I calculate the number of rows.
If anyone sees this and knows a better way to identify if visible data
present then I am interested.
If .SpecialCells(xlCellTypeVisible).Count /.Columns.Count > 1 Then
I will try to watch for replies from you but the MS communities site has not
been sending notifications for a week.
Sub SelectAutoFilteredData()
'Should always test for AutofilterMode and Filter Mode _
otherwise code can error out.
If Sheets("Sheet1").AutoFilterMode Then 'Test if filter arrows present
If Sheets("Sheet1").FilterMode Then 'Test if actually filtered
With Sheets("Sheet1").AutoFilter.Range
'Next line returns number of visible cells divided _
by number of columns in autofilter range _
If greater than 1 then some data is visible
If .SpecialCells(xlCellTypeVisible).Count /.Columns.Count > 1 Then
'Select visible data. Offset to row below column _
headers and resize to one row less to account for _
not including column headers.
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible).Select
End If
End With
End If
End If
End Sub
--
Regards,
OssieMac
"BlueWolverine" wrote:
> Hello,
> MS EXCEL 2003 on XP PRO.
>
> I have a macro (VBA handwritten) that is supposed to cycle through my tabs
> and apply an auto filter, copy and past the rows with an X in column I, and
> then paste those rows into another tab. It handles this very well if a tab
> has any X's, but if the filter returns zero ROWS, it copies the entire
> unfiltered data set to the results tab and hides them.
>
> Here's the code I've got. The code snippet with >> in front of it is where
> the error begins to occur.
>
>
> Sub Acquisition(tabname As String)
> Sheets(tabname).Select
> Rows("5:5").Select
> Selection.AutoFilter
> Selection.AutoFilter Field:=9, Criteria1:="<>"
> ActiveWindow.SmallScroll Down:=-39
>
> >>'Get this Range correct.
> Range("I5").Select
> ActiveCell.Offset(1, 0).Select
> Row1 = ActiveCell.Row
> lcv = 0
> While ActiveCell.Offset(lcv, 0).Value <> ""
> lcv = lcv + 1
> Wend
> Row2 = ActiveCell.Offset(lcv, 0).Row
> Rows(Row1 & ":" & Row2).Select
> Selection.Copy
>
> 'Move to results and find first blank row
> Sheets("Results").Select
> Range("A1").Offset(1, 0).Select
> lcv = 0
> While ActiveCell.Offset(lcv, 0).Value <> ""
> lcv = lcv + 1
> Wend
> ActiveCell.Offset(lcv, 0).Select
> ActiveSheet.Paste
> Application.CutCopyMode = False
> Row3 = ActiveCell.Row
> lcv = 0
> While ActiveCell.Offset(lcv, 0).Value <> ""
> lcv = lcv + 1
> Wend
>
> Range("N" & Row3 & ":N" & lcv + 1).Value = tabname
>
> Sheets(tabname).Select
> Rows("5:5").Select
> Selection.AutoFilter
>
> Thanks.
>
> --
> BlueWolverine
> MSE - Mech. Eng.
> Go BLUE!
|
Similar Threads
