Cell Range VBA

Carl Clements

Board Regular
Joined
Jun 13, 2008
Messages
95
Hi,

Can anybody help me with some code that will select a range of cells within a row, where the row number or ActiveCell will not always be the same.

In detail I have formulas in columns C:G and I need to select them and copy them down a number of rows, already pre-determined by a variable (CountLODH).

I have the following code that selects the formulas, but then fails on the second line with a Run-time error '1004': Method 'Range' of object'_Global failed

Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, CountLODH).Select

Thanks,
Carl
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

Code:
Selection.Resize(CountLODH).Select

but selecting is best avoided.
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,662
Maybe something like:
Code:
Sub FillFormula()
Dim CountLODH As Long   'Last row to fill the formula to
Dim FirstRow As Long    'First row to fill the formula
CountLODH = ActiveCell.Row  'Uses the ActiveCell row
FirstRow = Range("C" & Rows.Count).End(xlUp).Row + 1    'Uses first empty row in column C
If FirstRow > CountLODH Then Exit Sub   'If activecell.row < first empty row then exit sub
Range("C" & FirstRow & ":G" & CountLODH).Formula = Range("C2:G2").Formula 'Fills the range with the formula from C2:G2
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

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