Elementary range problem

ScottNiag

Active Member
Joined
Sep 3, 2002
Messages
260
Iam a a beginner, and I seem to be having a lot of trouble with ranges.I wonder if you could tell me what I am doing wrong in this example.
There is a table starting in cell A3 (headings), with 3 columns of data (length undetermined).I count the number of rows:
Sub clear2()
Dim NumRows As Integer
With Worksheets(1).Range("a3")
NumRows = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
So far, so good. The correct number of rows is counted. What I want to do next is select (or later on ClearContents)
the range (c4 to cNumrows).But what follows is wrong somehow.
.Range(.Offset(1, 2), .Offset(NumRows,
2)).Select
This to me should work. From A3 you offset 1 row, 2 columns, etc. But instead of selecting c4:c45, I get c6:c47 selected.
If I change the first offset so the line reads:
.Range(.Offset(-1, 2), .Offset(NumRows, 2)).Select
I get c4:c47 selected. It's almost acting as though the reference is anchored on A5 instead of A3.

Thx
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Very weird ! because, If I try your code, and use this:

MsgBox .Address
MsgBox .Offset(1, 2).Address

I get $A$3 and $C$4 as expected... what do you get when using those ?
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Well, I did get the range that you're getting, but I've not been able to explain it yet... this worked ok though:

.Offset(1, 2).Resize(NumRows - 4, 1)
 

ScottNiag

Active Member
Joined
Sep 3, 2002
Messages
260
Yes, I get the same thing. $a$3, then $c$4. Then, my next line is
.Range(.Offset(1, 2), .Offset(NumRows, 2)).Select

and I get c6:c47 selected.
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

It's very weird...try the Resize method, it looks cleaner too.
 

ScottNiag

Active Member
Joined
Sep 3, 2002
Messages
260
Do you mean that you TOO get c6:c47 selected?

Or do you get c4:c45 selected?
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

Yes, I get too C6:C47, but, only using the .Range(.Offset(1,2),.Offset(.....)) method. It's a VERY weird thing.
 

ScottNiag

Active Member
Joined
Sep 3, 2002
Messages
260
I ended up using this:

Dim NumRows As Integer
With Worksheets(1).Range("a3")
NumRows = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
End With
Range("c4:c" & NumRows + 3).ClearContents
 

ScottNiag

Active Member
Joined
Sep 3, 2002
Messages
260
Oh no, its fine! I guess I'm just sticking with a method that I am kind of used to, but you are right. I really SHOULD be expanding my use of other methods, so thanks for bringing it to my attention. Still stumped as to why the original idea didn't work though.
 

Forum statistics

Threads
1,148,280
Messages
5,745,829
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top