Filtered data selection using vba

  • Thread starter Thread starter sudhendra
  • Start date Start date
S

sudhendra

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
 
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
 
Thanks very much for the prompt reply

Yes i want only the visible 6th row and beyond to be
copied.


I am here putting the code which i was trying to do the
filter and the remaing task

Sub CorpBookFilter(cVol As Integer, sMaster, SelSh)
Select Case cVol
Case 1
cZone1 = 1
cZone2 = 2
Case 2
cZone1 = 3
cZone2 = 4
Case 3
cZone1 = 5
cZone2 = 6
Case 4
cZone1 = 7
cZone2 = 8
Case 5
cZone1 = 9
cZone2 = 10
End Select

Windows(FileIn).Activate
Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=4,
Criteria1:="=U.S.Summary"
Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=3, Criteria1:="="
Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=7, Criteria1:="="
Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=20, Criteria1:="="
Range("A1:AB" & Range("A2").End
(xlDown).Row).Select
Selection.Copy
Windows(sMaster).Activate
Sheets(SelSh).Select
Range("A1").Select
ActiveSheet.Paste
Windows(FileIn).Activate
Application.CutCopyMode = False
Range("A1:AB" & Selection.End
(xlDown).Row).AutoFilter

'to get the states
Range("A1:AB" & Selection.End
(xlDown).Row).AutoFilter Field:=3, Criteria1:="<>"

'to skip the US Summary,zones, districts
Range("C1").Select
Do While Len(ActiveCell.Value) = 0 And Len
(Range("A" & ActiveCell.Row).Value) > 0
ActiveCell.Offset(1, 0).Select
Loop

Range("A" & ActiveCell.Row & ":AB" &
Selection.End(xlDown).Row).Select
Selection.Copy
Windows(sMaster).Activate
Sheets(SelSh).Select
Range("A2").Select
ActiveSheet.Paste
Windows(FileIn).Activate
Application.CutCopyMode = False
Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter

' to get all the zones and districts

Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=4, Criteria1:="=0" &
cZone1

Range("G2").Select
Do While Len(ActiveCell.Value) = 0 And Len
(Range("A" & ActiveCell.Row).Value) > 0 'Or Len(Range("Q"
& ActiveCell.Row).Value) = "0" & ZoneNo
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
'Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=4, Criteria1:="=0" &
cZone1


', _
Operator:=xlAnd, Criteria2:="=0"
& cZone2 'Criteria1:="=0" & cZone1 and
Criteria:="=0" &cZone2
'Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=4, Criteria1:="<>0" &
cZone1, _
Operator:=xlAnd,
Criteria2:="<>0" & cZone2
'Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=4, Criteria1:="<>" '&
ZoneNo, _

'to skip U S Summary
' Range("P1").Select
' Do While Len(ActiveCell.Value) <> 0 And
Len(Range("A" & ActiveCell.Row).Value) > 0
' ActiveCell.Offset(1, 0).Select
' Loop
Range("A" & ActiveCell.Row).Select
Range("A" & ActiveCell.Row & ":AB" &
Selection.End(xlDown).Row).Select
Selection.Copy
Windows(sMaster).Activate
Sheets(SelSh).Select
Range("A" & Selection.End(xlDown).Row +
1).Select 'to insert after the last selected in crmrdata
sheet
ActiveSheet.Paste
Windows(FileIn).Activate
Application.CutCopyMode = False

' to get all the 2nd zone
Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter
Range("A1:AB" & Range("A2").End
(xlDown).Row).AutoFilter Field:=4, Criteria1:="=0" &
cZone2
''*********here is the filter where i want to copy from
visible 6th row and beyond ****************
Range("G14").Select
Do While Len(ActiveCell.Value) > 0 And Len
(Range("A" & ActiveCell.Row).Value) > 0 And
(ActiveCell.Offset(1, -2).Value) = "A" And
(ActiveCell.Offset(1, -3).Value) <> "0" & cZone2
'Do While And Len(Range("A" &
ActiveCell.Row).Value) > 0 'Or Len(Range("Q" &
ActiveCell.Row).Value) = "0" & ZoneNo
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select

Range("A" & ActiveCell.Row).Select
Range("A" & ActiveCell.Row & ":AB" &
Selection.End(xlDown).Row).Select

''''***************************************''
Selection.Copy
Windows(sMaster).Activate
Sheets(SelSh).Select
Range("A" & Selection.End(xlDown).Row +
1).Select 'to insert after the last selected in crmrdata
sheet
ActiveSheet.Paste
Windows(FileIn).Activate
Application.CutCopyMode = False

End Sub


Thanks once again
 
Thanks very much for the prompt reply

if i want to increment 5 times then how do i call this sub
 
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
 
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.
 
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")
 
Dear Tom

Thanks very much for your code not that i dont want to
use it i was experiment with the other code so i reposted
it I will retry your code now and get back to you.

Hey thanks again!!!
 
As I said, if your data isn't included in the Autofilter range, then no code
that uses that range will work - but if that is the problem, then you don't
need any code in the first place - you need to set up your filter range
properly.
 
Dear Tom

Greetings from Sudhendra

Here is the link where you could have a look at the data
i have filtered (its a jpeg).

http://groups.msn.com/suzzidba/work.msnw?
action=ShowPhoto&PhotoID=146

I tried your code again but could not work for this
particular data, i figured that if there the rows
displayed are not continus then the code does not work (i
mean to say from he example row 1, 5, 511 are not
continus)( I want to select from rows 515 and beyond)
I have another data where after filter displays row 1, 7,
8, 9..... then the code you have given works

Any thoughts....thanks once again

Sudhendra
 
Back
Top