Selecting Column of Visible AutoFiltered Cells.

R

Robert Christie

Hi

I have noticed in these posts the use of (xlCellTypeVisible) to select only
the filter data.
Can that be used to select only the filtered names in column A for copying
to a second sheet?

I have a list of 390 names in column A, a total of 12 room numbers in Column
L and a "Y" & "Z" code in column N.
I'm filtering the names by room number & a "Y" code.

The code I'm using at the moment is;

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B2")

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B38")

The Resize(400,1) is to ensure all names for a room are copied,
Question: What is the correct syntax to select only the visible names?

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro
 
T

Tom Ogilvy

By default only the visible data is copied. You don't generally need to use
specialcells and xlCellTypeVisible when copying. It appears your code
should work. What problem are you having.
 
R

Robert Christie

No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.
Click to expand...

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.

Tom Ogilvy said:
By default only the visible data is copied. You don't generally need to use
specialcells and xlCellTypeVisible when copying. It appears your code
should work. What problem are you having.

--
Regards,
Tom Ogilvy


Robert Christie said:
Hi

I have noticed in these posts the use of (xlCellTypeVisible) to select only
the filter data.
Can that be used to select only the filtered names in column A for copying
to a second sheet?

I have a list of 390 names in column A, a total of 12 room numbers in Column
L and a "Y" & "Z" code in column N.
I'm filtering the names by room number & a "Y" code.

The code I'm using at the moment is;

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B2")

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
Range("A1").Offset(1, 0).Resize(400, 1).Copy
Destination:=Worksheets _
("Results2004").Range("B38")

The Resize(400,1) is to ensure all names for a room are copied,
Question: What is the correct syntax to select only the visible names?

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro
Click to expand...
Click to expand...
 
T

Tom Ogilvy

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)
 
R

Robert Christie

Thanks Tom

And best wishes for the new Year.

Regards Bob C.


Tom Ogilvy said:
''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

--
Regards,
Tom Ogilvy

Robert Christie said:
No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.
Click to expand...
Click to expand...
 
R

Robert Christie

Hi Tom

Tried your code and I'm getting "Run-time error '1004'
Application-defined or Object-defined error
''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

--
Regards,
Tom Ogilvy

Robert Christie said:
No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.
Click to expand...
Click to expand...
 
T

Tom Ogilvy

bad editing job on my part.

set rng = rng.offset(1,0).Resize(rng.rows.count,-1)

should have no comma preceding the -1

set rng = rng.offset(1,0).Resize(rng.rows.count-1)

--
Regards,
Tom Ogilvy

Robert Christie said:
Hi Tom

Tried your code and I'm getting "Run-time error '1004'
Application-defined or Object-defined error
''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

--
Regards,
Tom Ogilvy

Robert Christie said:
No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of
Click to expand...
names
is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.
Click to expand...
Click to expand...
Click to expand...
 
R

Robert Christie

Hi Tom

Taking the comma out allowed the line to run , but it's copying all the
columns and I only require the names in column B to copy across to the second
sheet at Cell B2, B38, B74 etc.

Regards Bob C.

Tom Ogilvy said:
bad editing job on my part.

set rng = rng.offset(1,0).Resize(rng.rows.count,-1)

should have no comma preceding the -1

set rng = rng.offset(1,0).Resize(rng.rows.count-1)

--
Regards,
Tom Ogilvy

Robert Christie said:
Hi Tom

Tried your code and I'm getting "Run-time error '1004'
Application-defined or Object-defined error
''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

--
Regards,
Tom Ogilvy

No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure all
names are copied, ( number of names per room differ). Each group of names
is
copied to the same second sheet at different positions down column B. I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ogilvy

It remains a bad editing job, the correction should have been:

set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)

--
Regards,
Tom Ogilvy



Robert Christie said:
Hi Tom

Taking the comma out allowed the line to run , but it's copying all the
columns and I only require the names in column B to copy across to the second
sheet at Cell B2, B38, B74 etc.

Regards Bob C.

Tom Ogilvy said:
bad editing job on my part.

set rng = rng.offset(1,0).Resize(rng.rows.count,-1)

should have no comma preceding the -1

set rng = rng.offset(1,0).Resize(rng.rows.count-1)

--
Regards,
Tom Ogilvy

Robert Christie said:
Hi Tom

Tried your code and I'm getting "Run-time error '1004'
Application-defined or Object-defined error
on the line "set rng = rng.offset(1,0).Resize(rng.rows.count,-1)"


:

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

--
Regards,
Tom Ogilvy

No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to
Click to expand...
ensure
all
names are copied, ( number of names per room differ). Each group
Click to expand...
of
names
is
copied to the same second sheet at different positions down column
Click to expand...
B.
I'm
starting at the top and working down the column so as not to overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.
Click to expand...
Click to expand...
Click to expand...
 
R

Robert Christie

Thankyou Tom working Ok

Regards
Bob C.

Tom Ogilvy said:
It remains a bad editing job, the correction should have been:

set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)

--
Regards,
Tom Ogilvy



Robert Christie said:
Hi Tom

Taking the comma out allowed the line to run , but it's copying all the
columns and I only require the names in column B to copy across to the second
sheet at Cell B2, B38, B74 etc.

Regards Bob C.

Tom Ogilvy said:
bad editing job on my part.

set rng = rng.offset(1,0).Resize(rng.rows.count,-1)

should have no comma preceding the -1

set rng = rng.offset(1,0).Resize(rng.rows.count-1)

--
Regards,
Tom Ogilvy

Hi Tom

Tried your code and I'm getting "Run-time error '1004'
Application-defined or Object-defined error
on the line "set rng = rng.offset(1,0).Resize(rng.rows.count,-1)"


:

''' Tranfer Class No.4 names only
Sheets("Names").Select
[A1].Select
Selection.AutoFilter Field:=12, Criteria1:="04"
Selection.AutoFilter Field:=14, Criteria1:="Y"
set rng = Sheets("Names").Autofilter.Range
set rng = rng.offset(1,0).Resize(rng.rows.count,-1)
rng.copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

''' Tranfer Class No.5 names only
Selection.AutoFilter Field:=12, Criteria1:="05"
Selection.AutoFilter Field:=14, Criteria1:="Y"
rng.Copy
Destination:=Worksheets _
("Results2004").cells(rows.count,2).End(xlup)(2)

--
Regards,
Tom Ogilvy

No real problem Tom, I was just trying to copy the visible cells only.
Instead of including blank cells after the filtered names, to ensure
all
names are copied, ( number of names per room differ). Each group of
names
is
copied to the same second sheet at different positions down column B.
I'm
starting at the top and working down the column so as not to
overwrite.

Tom when you say:
By default only the visible data is copied.

Do you mean Excel copies only the visible cells, even though the
resizing
has selected past say the 36, 38 or 40 names filtered.

Thanks for the reply
Regards Bob C.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help me4 2
How do I choose the rows that are filtered ? 1
Looping through visible cells 10
Automated autofilter error 1
Selection.AutoFilter Issue 1
AutoFilter Criteria using named range 4
Excel 2003 autofiltering options from a userform 0
Autofilter turn off with macro 4

Top