Hi

I have filtered some data (they are filted from differnet
rows)

I want to skip the first 5 rows of this filtered data and
select the remaining data (as i cannot filter anyfurther)
copy it into another new sheet.

Can any one help please thanks inadvance

When filtered, some rows are hidden and some visible.

Do you want to select the 6th visible row and beyond to copy.

What kind of filter did you apply

Dim rng as Range, rng1 as range, cell as range
Dim i as long
set rng = Activesheet.Autofilter.range.Columns(1)
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
set rng1 = rng(rng.count)
set rng = rng.Specialcells(xlvisible)
i = 0
for each cell in rng
i = i + 1
if i = 6 then
range(cell,rng1).EntireRow.copy Destination:= _
Worksheets("Sheet2").Range("A1")
exit for
end if
Next


--
Regards,
Tom Ogilvy

"sudhendra" wrote in message
...
Hi

I have filtered some data (they are filted from differnet
rows)

I want to skip the first 5 rows of this filtered data and
select the remaining data (as i cannot filter anyfurther)
copy it into another new sheet.

Can any one help please thanks inadvance

I tried with this code below :-

The problem is if some of my rows are invisible like if
the data rows filtered are Like 1, 3,
7,12,111,112,113,114...897
I am unable to get the fourth and beyond ie., 111th row
to 897th row for selection.

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub


-----Original Message-----
When filtered, some rows are hidden and some visible.

Do you want to select the 6th visible row and beyond to
copy.

What kind of filter did you apply

Dim rng as Range, rng1 as range, cell as range
Dim i as long
set rng = Activesheet.Autofilter.range.Columns(1)
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
set rng1 = rng(rng.count)
set rng = rng.Specialcells(xlvisible)
i = 0
for each cell in rng
i = i + 1
if i = 6 then
range(cell,rng1).EntireRow.copy Destination:= _
Worksheets("Sheet2").Range("A1")
exit for
end if
Next


--
Regards,
Tom Ogilvy

"sudhendra" wrote
in message
...
Hi

I have filtered some data (they are filted from
differnet
rows)

I want to skip the first 5 rows of this filtered data
and
select the remaining data (as i cannot filter
anyfurther)
copy it into another new sheet.

Can any one help please thanks inadvance


.

There was an omitted statement in my code - this correct appears to work:

Sub CopyAfterfirstFive()
Dim rng As Range, rng1 As Range, cell As Range
Dim i As Long
Set rng = ActiveSheet.AutoFilter.Range.Columns(1).Cells
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
Set rng1 = rng(rng.Count)
Set rng = rng.SpecialCells(xlVisible)
i = 0
For Each cell In rng
i = i + 1
If i = 6 Then
Range(cell, rng1).EntireRow.Copy Destination:= _
Worksheets("Sheet2").Range("A1")
Exit For
End If
Next


End Sub

This does what you said you wanted. If you don't want to use it, then so be
it.

--
Regards,
Tom Ogilvy


"sudhendra" wrote in message
...
I tried with this code below :-

The problem is if some of my rows are invisible like if
the data rows filtered are Like 1, 3,
7,12,111,112,113,114...897
I am unable to get the fourth and beyond ie., 111th row
to 897th row for selection.

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub


-----Original Message-----
When filtered, some rows are hidden and some visible.

Do you want to select the 6th visible row and beyond to
copy.

What kind of filter did you apply

Dim rng as Range, rng1 as range, cell as range
Dim i as long
set rng = Activesheet.Autofilter.range.Columns(1)
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
set rng1 = rng(rng.count)
set rng = rng.Specialcells(xlvisible)
i = 0
for each cell in rng
i = i + 1
if i = 6 then
range(cell,rng1).EntireRow.copy Destination:= _
Worksheets("Sheet2").Range("A1")
exit for
end if
Next


--
Regards,
Tom Ogilvy

"sudhendra" wrote
in message
...
Hi

I have filtered some data (they are filted from
differnet
rows)

I want to skip the first 5 rows of this filtered data
and
select the remaining data (as i cannot filter
anyfurther)
copy it into another new sheet.

Can any one help please thanks inadvance


.

After closely reading your postings, it sounds like you are not properly
applying the filter (a filter can filter the entire sheet). If you have
blank rows in your data, you need to select all your data and then apply the
filter. If You still want to copy filled rows beyond the autofilter range,
you can do:

Dim rng as Range, rng1 as range
set rng = Activesheet.Autofilter.range.Columns(1).cells
set rng = rng.offset(1,0)
set rng1 = rng(rng.count)
set rng = Cells(rows.count,1).End(xlup)
range(rng1,rng).EntireRow.copy Destination:= _
Worksheets("Sheet2").Range("A1")

--
Regards,
Tom Ogilvy




"sudhendra" wrote in message
...
I tried with this code below :-

The problem is if some of my rows are invisible like if
the data rows filtered are Like 1, 3,
7,12,111,112,113,114...897
I am unable to get the fourth and beyond ie., 111th row
to 897th row for selection.

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub


-----Original Message-----
When filtered, some rows are hidden and some visible.

Do you want to select the 6th visible row and beyond to
copy.

What kind of filter did you apply

Dim rng as Range, rng1 as range, cell as range
Dim i as long
set rng = Activesheet.Autofilter.range.Columns(1)
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
set rng1 = rng(rng.count)
set rng = rng.Specialcells(xlvisible)
i = 0
for each cell in rng
i = i + 1
if i = 6 then
range(cell,rng1).EntireRow.copy Destination:= _
Worksheets("Sheet2").Range("A1")
exit for
end if
Next


--
Regards,
Tom Ogilvy

"sudhendra" wrote
in message
...
Hi

I have filtered some data (they are filted from
differnet
rows)

I want to skip the first 5 rows of this filtered data
and
select the remaining data (as i cannot filter
anyfurther)
copy it into another new sheet.

Can any one help please thanks inadvance


.

Tom Ogilvy gave me this code to increment down one cell at a time in a
single column on a filtered sheet. I have this as a separate macro and
insert a call to it when I need to go down a cell. It works great for me.

Ed

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

"sudhendra" wrote in message
...
Hi

I have filtered some data (they are filted from differnet
rows)

I want to skip the first 5 rows of this filtered data and
select the remaining data (as i cannot filter anyfurther)
copy it into another new sheet.

Can any one help please thanks inadvance

Thanks very much for the prompt reply

if i want to increment 5 times then how do i call this sub




-----Original Message-----
Tom Ogilvy gave me this code to increment down one cell
at a time in a
single column on a filtered sheet. I have this as a
separate macro and
insert a call to it when I need to go down a cell. It
works great for me.

Ed

Sub Increment1()
Dim rng As Range, rng1 As Range
Dim icol As Long
icol = ActiveCell.Column
Set rng = ActiveSheet.AutoFilter.Range
Set rng = Intersect(rng, Columns(icol))
Set rng = Range(ActiveCell.Offset(1, 0), rng(rng.Count))
On Error Resume Next
Set rng1 = rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1(1).Select
End If
End Sub

"sudhendra" wrote
in message
...
Hi

I have filtered some data (they are filted from
differnet
rows)

I want to skip the first 5 rows of this filtered data
and
select the remaining data (as i cannot filter
anyfurther)
copy it into another new sheet.

Can any one help please thanks inadvance


.