Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mouse click not selecting correct data point | Charts and Charting in Excel | |||
Selecting and applying the correct forumla | Excel Worksheet Functions | |||
Need urgent help selecting correct test!! | Excel Worksheet Functions | |||
Selecting the correct number from a range of cells | Excel Worksheet Functions | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) |