Copying visible cells in Excel using VBA

[angiemeh]

I have a macro where I'm using an autofilter. I use:
'Range(Selection, Selection.End(xlDown)).Select and it works great, except for when it can't find the condition.

If I"m looking for 'Sugar' in the filter and 'Sugar' exists in the data set, the filter pulls all sugar items, copies this data and puts it in another workbood. I use the xlDown and it works. If 'Sugar' does not exist then the filter shows nothing (which is correct) but I want the macro to know that if 'Sugar' doesn't exist then It doesn't need to copy the data into another workbook.

If filter shows nothing then perform action item 1,2,3 and then end. If filter shows items, then do 1,2,3,4,5,6,7, and then end.

With VBA code, how can I check if there is data in the visible cells and if not, then don't copy? (still want to do some steps, but skip steps 4,5,6,7)


I

 

[Mike Blackman]

Hi,

You could use this or some variation of maybe;

Dim lRow as Long

lRow = Range("A" & Rows.Count).End(xlup).Row

Range("A1:A" & lRow).SpecialCells(xlCellTypeVisible).Copy

 

[angiemeh]

That may be on the right track, but I'm still having issues. The 1st 2 rows are header rows. Row 2 says (first name, last name, commodity, etc)

I only want to copy columns B through F, so I changed the formula below.

The problem is when it doesn't find the commodity, I just see 2 header rows (it copied row 2 when I ran the code)

Here is what I put:
Dim lRow As Long

lRow = Range("B" & Rows.Count).End(xlUp).Row

Range("B3:F" & lRow).SpecialCells(xlCellTypeVisible).Copy

1) I tried putting lRow = Range("B3" & Rows.Count).End(xlUp).Row, but that didn't work. I don't know what Rows.count does, so I didn't know what I was doing I tried other variations, but can't figure out how to ignore the header rows...

Right now the autofilter is on row 2. The data starts in row 3. Should I change where the autofilter is?

 

[Greg Truby]

Have a look at this thread from a couple of years ago.

http://www.mrexcel.com/forum/showthread.php?t=173854

 

[angiemeh]

Perfect!
Here is what I came up with from reading that:

I set the Auto-filter to span 100 rows (knowing I won't get more than this), then I spedified the range b3:f100 to be the same as the range of the auto-filter.

Here is the code below:

On Error GoTo TheEnd:
Range("b3:f100").SpecialCells(xlCellTypeVisible).Select
Selection.Copy
-Then I activate the other workbook, paste and then reset the auto-filters on this sheet

TheEnd:
Activate the other workbook

End Sub

Thanks!