Selecting the correct range
 

Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and copy them
to another worksheet (SS A). The problem is it only does A to V. (Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False


Worksheets("SS A").Range("A:IV").ClearContents



'With Worksheets("Panel Details").Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With


Application.EnableEvents = True

Selecting the correct range
 

This line is wrong
Worksheets("SS A").Range("A:IV").ClearContents
should be
Worksheets("SS A").Range("A:AN").ClearContents


"Trever B" wrote:

Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and copy them
to another worksheet (SS A). The problem is it only does A to V. (Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False


Worksheets("SS A").Range("A:IV").ClearContents



'With Worksheets("Panel Details").Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With


Application.EnableEvents = True

Selecting the correct range
 

I used A:IV to completly clear all cells (Just in case anyone typed into
them) before coping into it. Would that make all the difference?

"Joel" wrote:

This line is wrong
Worksheets("SS A").Range("A:IV").ClearContents
should be
Worksheets("SS A").Range("A:AN").ClearContents


"Trever B" wrote:

Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and copy them
to another worksheet (SS A). The problem is it only does A to V. (Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False


Worksheets("SS A").Range("A:IV").ClearContents



'With Worksheets("Panel Details").Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With


Application.EnableEvents = True

Selecting the correct range
 

Try this
Worksheets("SS A").Range("A:AN").ClearContents
Worksheets("SS A").Range("A:IV").Select

"Trever B" wrote:

I used A:IV to completly clear all cells (Just in case anyone typed into
them) before coping into it. Would that make all the difference?

"Joel" wrote:

This line is wrong
Worksheets("SS A").Range("A:IV").ClearContents
should be
Worksheets("SS A").Range("A:AN").ClearContents


"Trever B" wrote:

Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and copy them
to another worksheet (SS A). The problem is it only does A to V. (Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False


Worksheets("SS A").Range("A:IV").ClearContents



'With Worksheets("Panel Details").Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With


Application.EnableEvents = True

Selecting the correct range
 

It could indeed have to do with blank columns. Go to A1 and press
Ctrl+Shift+*. This would select your CurrentRegion (=area completely
surrounded by blank cells). If this selection does not extend to column AN,
then you probably have a blank column before column AN. Deleting the blank
column(s) should solve your problem.

Cheers,

Joerg


"Trever B" wrote in message
...
Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and copy
them
to another worksheet (SS A). The problem is it only does A to V. (Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False


Worksheets("SS A").Range("A:IV").ClearContents



'With Worksheets("Panel
Details").Range("A1").CurrentRegion
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With


Application.EnableEvents = True

Selecting the correct range
 

There are two ranges available, CurrentRegion and UsedRange.
Ctrl+Shift+End takes you to the lower right corner of Used Range. You
only need to clear in that range.

Put the cursor in the upper left cell you want to clear. Press Ctrl
+Shift+End to select UsedRange, then Delete
By code,
With Range("A1")


On Mar 7, 3:53 am, "Joerg" wrote:
It could indeed have to do with blank columns. Go to A1 and press
Ctrl+Shift+*. This would select your CurrentRegion (=area completely
surrounded by blank cells). If this selection does not extend to column AN,
then you probably have a blank column before column AN. Deleting the blank
column(s) should solve your problem.

Cheers,

Joerg

"Trever B" wrote in message

...



Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and copy
them
to another worksheet (SS A). The problem is it only does A to V. (Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False

Worksheets("SS A").Range("A:IV").ClearContents

'With Worksheets("Panel

Details").Range("A1").CurrentRegion



.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Application.EnableEvents = True- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Selecting the correct range
 

Fat fingers. Accidentally posted before I was done. Here's the whole
post.

There are two ranges available, CurrentRegion and UsedRange.
Ctrl+Shift+End takes you to the lower right corner of Used Range. You
only need to clear in that range.

Put the cursor in the upper left cell you want to clear. Press Ctrl
+Shift+End to select UsedRange, then Delete

By code,
With Range("A1")
Range(.Cells, .SpecialCells(xlLastCell)).ClearContents
End With

Carl.

On Mar 8, 9:08 am, "Carl Hartness" wrote:
There are two ranges available, CurrentRegion and UsedRange.
Ctrl+Shift+End takes you to the lower right corner of Used Range. You
only need to clear in that range.

Put the cursor in the upper left cell you want to clear. Press Ctrl
+Shift+End to select UsedRange, then Delete
By code,
With Range("A1")

On Mar 7, 3:53 am, "Joerg" wrote:



It could indeed have to do with blank columns. Go to A1 and press
Ctrl+Shift+*. This would select your CurrentRegion (=area completely
surrounded by blank cells). If this selection does not extend to column AN,
then you probably have a blank column before column AN. Deleting the blank
column(s) should solve your problem.

Cheers,

Joerg

"Trever B" wrote in message

...

Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and copy
them
to another worksheet (SS A). The problem is it only does A to V. (Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False

Worksheets("SS A").Range("A:IV").ClearContents

'With Worksheets("Panel

Details").Range("A1").CurrentRegion

.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Application.EnableEvents = True- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Selecting the correct range
 

Carl, did you accidentally post before reading Trever's problem?

SCNR

Joerg


"Carl Hartness" wrote in message
ups.com...
Fat fingers. Accidentally posted before I was done. Here's the whole
post.

There are two ranges available, CurrentRegion and UsedRange.
Ctrl+Shift+End takes you to the lower right corner of Used Range. You
only need to clear in that range.

Put the cursor in the upper left cell you want to clear. Press Ctrl
+Shift+End to select UsedRange, then Delete

By code,
With Range("A1")
Range(.Cells, .SpecialCells(xlLastCell)).ClearContents
End With

Carl.

On Mar 8, 9:08 am, "Carl Hartness" wrote:
There are two ranges available, CurrentRegion and UsedRange.
Ctrl+Shift+End takes you to the lower right corner of Used Range. You
only need to clear in that range.

Put the cursor in the upper left cell you want to clear. Press Ctrl
+Shift+End to select UsedRange, then Delete
By code,
With Range("A1")

On Mar 7, 3:53 am, "Joerg" wrote:



It could indeed have to do with blank columns. Go to A1 and press
Ctrl+Shift+*. This would select your CurrentRegion (=area completely
surrounded by blank cells). If this selection does not extend to
column AN,
then you probably have a blank column before column AN. Deleting the
blank
column(s) should solve your problem.

Cheers,

Joerg

"Trever B" wrote in message

...

Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and
copy
them
to another worksheet (SS A). The problem is it only does A to V.
(Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False

Worksheets("SS A").Range("A:IV").ClearContents

'With Worksheets("Panel

Details").Range("A1").CurrentRegion

.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Application.EnableEvents = True- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Selecting the correct range
 

I did read Trevor's note. He is having trouble getting all the
cells. CurrentRegion and CtrlShift* don't get all the cells.
UsedRange and CtrlShiftEnd do.

What's SCNR?

On Mar 8, 8:27 pm, "Joerg" wrote:
Carl, did you accidentally post before reading Trever's problem?

SCNR

Joerg

"Carl Hartness" wrote in message

ups.com...



Fat fingers. Accidentally posted before I was done. Here's the whole
post.

There are two ranges available, CurrentRegion and UsedRange.
Ctrl+Shift+End takes you to the lower right corner of Used Range. You
only need to clear in that range.

Put the cursor in the upper left cell you want to clear. Press Ctrl
+Shift+End to select UsedRange, then Delete

By code,
With Range("A1")
Range(.Cells, .SpecialCells(xlLastCell)).ClearContents
End With

Carl.

On Mar 8, 9:08 am, "Carl Hartness" wrote:
There are two ranges available, CurrentRegion and UsedRange.
Ctrl+Shift+End takes you to the lower right corner of Used Range. You
only need to clear in that range.

Put the cursor in the upper left cell you want to clear. Press Ctrl
+Shift+End to select UsedRange, then Delete
By code,
With Range("A1")

On Mar 7, 3:53 am, "Joerg" wrote:

It could indeed have to do with blank columns. Go to A1 and press
Ctrl+Shift+*. This would select your CurrentRegion (=area completely
surrounded by blank cells). If this selection does not extend to
column AN,
then you probably have a blank column before column AN. Deleting the
blank
column(s) should solve your problem.

Cheers,

Joerg

"Trever B" wrote in message

...

Hi,

Thanks in advance

In the following code it is supposed to select Colums A to AN and
copy
them
to another worksheet (SS A). The problem is it only does A to V.
(Could
have something to do with blank rows/Columns?)

How do I change the Current region to include A to AN

Application.EnableEvents = False

Worksheets("SS A").Range("A:IV").ClearContents

'With Worksheets("Panel

Details").Range("A1").CurrentRegion

.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With

Application.EnableEvents = True- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -