Copy xlCellTypeVisible if blank

Closed
Report
Posts
11
Registration date
Monday May 10, 2010
Status
Member
Last seen
December 9, 2011
-
Posts
11
Registration date
Monday May 10, 2010
Status
Member
Last seen
December 9, 2011
-
Hi,
I have a workbook where I am FILTERing data then coping the visable range, after the headers D1 & D2) to another sheet with this macro

Sheets("IMPORT FROM CONEST").Select

Range(("D3"), Range("AF65536").End(xlUp)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

Sheets("BID ITEMS- LABOR HR").Select

Range("B9").Select
ActiveSheet.Paste

Sometimes the filtering results in no data to copy - which is correct. BUT when the macro see blank data in the range it takes row 2 which is part of the header.

How do I stop the copy and paste if there is no data in the visable range, please?



1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
I have not seen the data or the full macro .
my assumption is the row 1 and 2 are column headings and data starts from third row.

however my suggestion is
after the line

Range(("D3"), Range("AF65536").End(xlUp)).Select 


introduce aline

If Selection.SpecialCells(xlCellTypeVisible).Cells(1, 1) = "" Then Exit Sub


and then come the rest of the macro

check whether it help-s

the new lines gives the first cells in the first column of the filtered data.
Posts
11
Registration date
Monday May 10, 2010
Status
Member
Last seen
December 9, 2011

Thanks - I want to skip over the copy and paste if data ="" not exit the sub