Defining a Range

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
I need to select a range, but it is in the middle of a column. For example, I have a macro that will take a formula in cell F2 and copy it into a range from F3 to the end of the column. I already have defined a variable called LastRow, however I cannot seem to get the syntax right for the Range.Select statement. What I tried was:

Range(F3,"F"&LastRow).Select

I appreciate your patience with this newbie question.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
That didn't work. My statement looks like this:

Range("F3:F" & LastRow).Select

I got the Runtine error 1004. I am using Excel XP if that helps. Thanks.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Can you post the rest of the code?
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

Is LastRow referring to the row number or the last cell? I was assuming it was a row number.
 

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
Here is the relevant piece of the code:

'Create conversion formula
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="" "",IF(RC[-2]="" "",ABS(RC[-1]),"" ""),"" "")"

NOTE: This picks only the rows that meet the relevant criteria (which is copy the formula if both Columns B and D have no data).

'Copy formula into rest of column
Range("F2").Select
Selection.Copy
Range("F3:F" & LastRow).Select
ActiveSheet.Paste

NOTE: This should :oops: copy the formula in F2 to the remainder of Column F.

With the particular dataset I'm using for testing, LastRow has a value of 1105. So I "thought" I was copying the formula from cell F2 to cells F3 through F1105.

Where am I messing up?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
I think we need to see where LastRow is coming from.

By the way that code could be replaced with this one-liner.
Code:
 Range("F2:F" & LastRow)..FormulaR1C1 = _
"=IF(RC[-4]="" "",IF(RC[-2]="" "",ABS(RC[-1]),"" ""),"" "")"
If that doesn't work then I really think the problem is with how you've set the value for LastRow.
 

madkinson

Board Regular
Joined
Dec 17, 2004
Messages
101
AARRGGHH I forgot to define it in the subroutine that I broke out of the main macro, so you were absolutely right that LastRow is the problem. Once I tied the value to the main routine, it ran like a charm. Thanks!!

:oops: :oops: :oops:
 

Forum statistics

Threads
1,141,681
Messages
5,707,795
Members
421,528
Latest member
datdude151

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