VBA Range Reference Question

[elliott740]

I am using a FOR/NEXT loop that is copying data from elsewhere to specific cells in a Summary Sheet. I want to draw a border around the data I just put in these columns. When I was testing I used the following:
Worksheets("Sales").Range("E5:J30").Borders(xlEdgeLeft).Weight = xlMedium
Worksheets("Sales").Range("E5:J30").Borders(xlEdgeRight).Weight = xlMedium
Worksheets("Sales").Range("E5:J30").Borders(xlEdgeTop).Weight = xlMedium
Worksheets("Sales").Range("E5:J30").Borders(xlEdgeBottom).Weight = xlMedium

This worked perfectly but how can I remove the hardcoding of E5:J30? The Columns (E-J) are constant and so is the starting row (5) but the 30 is variable (may be 10 rows, may be 50...). I do know what row I stoped loading data.

Please Help,

Tom

 

[mae0429]

You'll need to "auto-detect" the last row of data (assuming that row will be the last row in your sheet) and then use a variable to refer to the last row in your reference. I did this a little while back, but it escapes me as of late...will post if I can find it.

 

[Smitty]

Here's one way:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#00007F">Set</SPAN> rng = Worksheets("Sales").Range([D5], Cells(Rows.Count, "J").End(xlUp))<br>        <br>        <SPAN style="color:#00007F">With</SPAN> rng<br>            .Borders(xlEdgeLeft).Weight = xlMedium<br>            .Borders(xlEdgeRight).Weight = xlMedium<br>            .Borders(xlEdgeTop).Weight = xlMedium<br>            .Borders(xlEdgeBottom).Weight = xlMedium<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[elliott740]

I tried to do the following but am getting a run-time 1004 error:
Rng = """E5" & RowCounter & ":JC" & LoopCounter & """"
This equates to "E5:JC42" at runtime
here is where it fails:
Worksheets("Sales").Range(Rng).Borders(xlEdgeLeft).Weight = xlMedium

What did I do wrong?
Why can't I use my variable rng?

 

[mae0429]

Did you use "Set Rng = ....." or just type "Rng = ....."

 

[elliott740]

Just rng = ...
What does the Set do? Should I try that?

 

[mae0429]

I believe you need to set Ranges and Variants using the Set part (why I do not know, I just do it and it works :P )

Give it a whirl, if it doesn't work, wait for Smitty to come back: he's solved a lot for me in the past.

 

[elliott740]

Instead of:
Rng = """E5" & RowCounter & ":JC" & LoopCounter & """"
I tried:
Set Rng = """E5" & RowCounter & ":JC" & LoopCounter & """"
Now it fails on this line with a Run-time error 13
(I did not use a Dim for the variable Rng)
Tom

 

[mae0429]

You'll need to: Dim Rng As Range
and then you can set it later with: Set Rng =

 

[Smitty]

You need to use Set with range objects to "Set" the reference. From the helpfile:

Use the Set statement to assign an object to an object variable.

Regarding: Rng = """E5" & RowCounter & ":JC" & LoopCounter & """"

This equates to "E5:JC42" at runtime

I don't see how that can work, as it'll throw an invalid range reference at the E5 & RowCounter part. It's also a pretty clunky way to go about it, although I see what you were shooting for. Also note the "JC"; in your original post you only mentioned E:J.

Using the Cells method of referring to ranges is really handy, so you might want to take a look at what I posted.

EDIT:

Now it fails on this line with a Run-time error 13
(I did not use a Dim for the variable Rng)

First, what's the error? Second, you should always declare your variables. It's really good coding practice and will make your code faster; without it VBA needs to evaluate your variables at runtime and figure out what it thinks the best setting is for them. See "Declaring Variables" in the VB helpfile for a better explanation than I can give.