Filtered Range in Macro - Need to Drop First Row

[lemo]

it's probably trivial, but i can't get rid of that first row of my filtered range.

i have this statement:

Set RngVis = ActiveSheet.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible)

which correctly gives me only the visible filtered cells, but includes the variable names row, which i don't want.
(gives me this range, for example - $A$3:$Z$3,$A$12:$Z$17,$A$55:$Z$74 - but i'd like to drop the 3rd row)

i tried RngVisNoHeaders = RngVis.Offset(1, 0) but it just shifts everything down by one, i.e. row 3 becomes 4, etc..

thanks,
len

 

[Brianwarnock]

I have never done this and cannot find what you have done in help but I will take an educated guess that you can selwct the range try

Dim lastrow as long
Dim lastcol as long

lastrow = Sheets("Sheet1").UsedRange.Rows.Count
lastcol = Sheets("Sheet1").UsedRange.Columns.Count

Set RngVis = ActiveSheet.Range(cells(4,1),cells(lastrow,lastcol)).AutoFilter.Range.Cells.SpecialCells(xl CellTypeVisible)

Brian

 

[lemo]

thanks Brian.
(i feel like you alone are manning the shop these days)

this worked, but not in a way i needed.
i already have a filtered range. i just need my macro to copy the filtered (visible) cells when i 'filter around', so i can paste them into another tab and/or file, but without the header row.
l

 

[Brianwarnock]

Hmm

I had thought that it might give you

$A$12:$Z$17,$A$55:$Z$74

as it started the range in row 4.

If what you are doing is copying non hidden rows then you could use the hidden property to test if a row is visible or not.

If Rows(rownum).Hidden = False Then

Brian

 

[lemo]

figured it out!
or, rather, found this idea online, which worked here. very simple, as usual -

RngVis = Intersect(ActiveSheet.Range("A4:Z1000"), _
ActiveSheet.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible))

provided row 3 is your header row and A3:Z1000 is filtered range.

l

 

[Brianwarnock]

Ah!
I think I understand why my code would need the Intersect, but don't undestand why the posted code does not require the Set.

BTW didn't realise that your range was fixed not dynamic.

Brian

 

[lemo]

ok, my final code is -

Set rngvis = Intersect(ActiveSheet.Range("A4:AD" & lastrow), _
ActiveSheet.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible))

so it does include 'Set', and it is dynamic.
i just wanted to share the main idea (Intersect...) immediately, was overwhelmed with joy, forgot all the other details.

l