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!

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!

With some modification and adaptation that worked rather well. I like that
you grab all visible records without a loop and using only a couple lines of
code.

Thanks!
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


"OssieMac" wrote:

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!