Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addressing cells without selecting
Hi everyone
I'm trying to find the last cell of data in Column B, offset 1 row down and 1 column left, then resize down 200 rows in column A and clearcontents. My attempt so far, selects all cells from A2 down 200 rows past the last cell in Column B. I'm trying to achieve this without selecting ranges. Code so far; With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset(1, - 1).Resize(200, 1)).Select End With Note!! "Select" is in code just for testing. TIA Regards Bob C. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addressing cells without selecting
Hi
is this what you're looking for?? With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset(1, - 1).Resize(200, 1)).Clearcontents End With -- Regards Frank Kabel Frankfurt, Germany Robert Christie wrote: Hi everyone I'm trying to find the last cell of data in Column B, offset 1 row down and 1 column left, then resize down 200 rows in column A and clearcontents. My attempt so far, selects all cells from A2 down 200 rows past the last cell in Column B. I'm trying to achieve this without selecting ranges. Code so far; With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset(1, - 1).Resize(200, 1)).Select End With Note!! "Select" is in code just for testing. TIA Regards Bob C. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addressing cells without selecting
Hi Bob
Try this one instead: With ActiveSheet .Cells(.Rows.Count, "B").End(xlUp). _ Offset(1, -1).Resize(200, 1).ClearContents End With -- Best Regards Leo Heuser Followup to newsgroup only please. "Robert Christie" skrev i en meddelelse ... Hi everyone I'm trying to find the last cell of data in Column B, offset 1 row down and 1 column left, then resize down 200 rows in column A and clearcontents. My attempt so far, selects all cells from A2 down 200 rows past the last cell in Column B. I'm trying to achieve this without selecting ranges. Code so far; With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset(1, - 1).Resize(200, 1)).Select End With Note!! "Select" is in code just for testing. TIA Regards Bob C. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addressing cells without selecting
Try the following. Correct for wordwrap.
With ActiveSheet ..Range("B" & .Rows.Count).End(xlUp).Offset(1, -1).Resize (200, 1).ClearContents End With Regards, Greg -----Original Message----- Hi everyone I'm trying to find the last cell of data in Column B, offset 1 row down and 1 column left, then resize down 200 rows in column A and clearcontents. My attempt so far, selects all cells from A2 down 200 rows past the last cell in Column B. I'm trying to achieve this without selecting ranges. Code so far; With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset(1, - 1).Resize(200, 1)).Select End With Note!! "Select" is in code just for testing. TIA Regards Bob C. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addressing cells without selecting
No Frank
That will clear all my data in columns A and B from below title row. Note!! "Select" is in code just for testing Thanks Regards Bob C. -----Original Message----- Hi is this what you're looking for?? With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset (1, - 1).Resize(200, 1)).Clearcontents End With -- Regards Frank Kabel Frankfurt, Germany Robert Christie wrote: Hi everyone I'm trying to find the last cell of data in Column B, offset 1 row down and 1 column left, then resize down 200 rows in column A and clearcontents. My attempt so far, selects all cells from A2 down 200 rows past the last cell in Column B. I'm trying to achieve this without selecting ranges. Code so far; With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset (1, - 1).Resize(200, 1)).Select End With Note!! "Select" is in code just for testing. TIA Regards Bob C. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addressing cells without selecting
Hi
not quite sure but maybe you're looking for this? With ActiveSheet Cells(.Rows.Count, "B").End(xlUp).Offset(1, - 1).Resize(200, 1).Clearcontents End With -- Regards Frank Kabel Frankfurt, Germany Robert Christie wrote: No Frank That will clear all my data in columns A and B from below title row. Note!! "Select" is in code just for testing Thanks Regards Bob C. -----Original Message----- Hi is this what you're looking for?? With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset (1, - 1).Resize(200, 1)).Clearcontents End With -- Regards Frank Kabel Frankfurt, Germany Robert Christie wrote: Hi everyone I'm trying to find the last cell of data in Column B, offset 1 row down and 1 column left, then resize down 200 rows in column A and clearcontents. My attempt so far, selects all cells from A2 down 200 rows past the last cell in Column B. I'm trying to achieve this without selecting ranges. Code so far; With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset (1, - 1).Resize(200, 1)).Select End With Note!! "Select" is in code just for testing. TIA Regards Bob C. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addressing cells without selecting
A big thankyou to Frank, Leo & Greg.
I'll get on top of this VBA sometime, if I live long enough. Thanks again Regards Bob C. -----Original Message----- Hi not quite sure but maybe you're looking for this? With ActiveSheet Cells(.Rows.Count, "B").End(xlUp).Offset(1, - 1).Resize(200, 1).Clearcontents End With -- Regards Frank Kabel Frankfurt, Germany Robert Christie wrote: No Frank That will clear all my data in columns A and B from below title row. Note!! "Select" is in code just for testing Thanks Regards Bob C. -----Original Message----- Hi is this what you're looking for?? With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End(xlUp).Offset (1, - 1).Resize(200, 1)).Clearcontents End With -- Regards Frank Kabel Frankfurt, Germany Robert Christie wrote: Hi everyone I'm trying to find the last cell of data in Column B, offset 1 row down and 1 column left, then resize down 200 rows in column A and clearcontents. My attempt so far, selects all cells from A2 down 200 rows past the last cell in Column B. I'm trying to achieve this without selecting ranges. Code so far; With ActiveSheet Range("B2", .Cells(.Rows.Count, "B").End (xlUp).Offset (1, - 1).Resize(200, 1)).Select End With Note!! "Select" is in code just for testing. TIA Regards Bob C. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting multiple cells, I can't see the cells highlighted | Excel Discussion (Misc queries) | |||
How to change shade of cells when selecting multiple cells | Excel Discussion (Misc queries) | |||
By selecting cells adjacent to cells tally sheet | Excel Worksheet Functions | |||
absolute addressing and inserting new cells | Excel Discussion (Misc queries) | |||
Addressing cells on inactive sheets | Excel Discussion (Misc queries) |