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.

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.

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.

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.

.

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.
.

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.
.

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.
.
.