VBA: Selecting all visible cells

[getoffthehill]

Hi,

I have a number of visible rows in my worksheet (and a number of hidden rows). I want to copy over the visible rows only. My data will be of varying lenghts.
The problem is, when I run my macro it only selects rows 1 & 2 and stops short of selecting all visible rows.
My Macro looks like the following:

Range("A1:G1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Range("A288").Select
ActiveSheet.Paste

When recording the Macro I firstly selected A1 to G1, then held 'Shift' & 'Ctrl' to select all. I had to do this twice to select everything - as doing it once only selected rows 1 & 2. It seems to me that the row in Red above is doing nothing in my Macro, yet it worked when recording the Macro.

I'd appreciate any help.

Thanks.

 

[Jon von der Heyden]

Perhaps:

[COLOR="Blue"]Dim[/COLOR] rng [COLOR="Blue"]As[/COLOR] Range
[COLOR="Blue"]Set[/COLOR] rng = Application.Intersect(ActiveSheet.UsedRange, Range("A1:G287"))
rng.SpecialCells(xlCellTypeVisible).Copy Destination:=Range("A288")

 

[getoffthehill]

Thanks for the below.

I actually realised I need to copy & paste my data to another sheet so I now have the following which is working well:

Range("A1:G1").Select
Dim rng As Range
Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A1:G287"))
rng.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste

The problem is that the data is of varying lenght. So it could exceed Row 287 as is called out in the above. Is there any way to ensure it copies all cells?

Thanks again for the help.

 

[Jon von der Heyden]

Try;

Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A:G"))

 

[getoffthehill]

Thank you very much for your help.

Sorted now