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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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 ?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top