Range to increase/decrease with data

[Guest]

Hi,
If column B had data ranging from Row 1 to Row 100 say, I would then want my
range in Column A to increase/decrease to match the number of rows Column B
encompasses.

Can someone help with a VBA code to do this for me?

(The Range would consist of formulas that work off the data in Column B)

Many Thanks

John

 

[Tim Marsh]

unless you really want to use VBA, you could instead use a dynamic range,
based on column B. this will only work as long as there are no empty cells
in column B.

the following formula should be inserted into the 'refers to' box of the
'Define Name' form (Insert -> Names -> Define): -

=offset(Sheet1!$A$1,0,0,counta($B:$B),1)

this counts how many non-blank cells are in column b and selects that same
number of cells in column A (starting from cell A1). if you have header
rows that you do not want to include in the selection, you can
'counta($B:$B)-1' to remove 1 row from the total count.

hope that makes some kind of sense!

tim

 

[xLBaron]

Hey JohnUK -

Try this-

Sub Copy_Down()

Range("a1").Copy Range("a1", Range("b1").End(xlDown).Offset(0, -1))

End Sub

This will copy the formula in cell A1 down until it comes to the end of
column B's data

 

[Tim Marsh]

apologies for replying to my own post, but i should also have pointed out
that following on from what i said below, you need to assign a name to the
newly created dynamic range, then use that range name for whatever your
original purpose was!

hth,

tim

"Tim Marsh" <tmarsh-trousers-@-take off my trousers to
reply-blueyonder.co.uk> wrote in message

 

[Guest]

Hi Tim
I wasn’t to sure at first because I couldn’t see the Range on the sheet (by
minimising the view to 25%, nor from the window dropdown) but after I entered
the code into my VBA project it worked a treat.
This is how the code looks:
(I called the Range “Rangeâ€)

Range("A2").Select
ActiveCell.FormulaR1C1 =
RC[2]&IF(Report!R2C2=""All"",""All"",RC[6])&RC[14]&IF(Report!R3C2=""All"",""All"",RC[3])"
Selection.Copy
Application.Goto Reference:="Range"
Selection.PasteSpecial Paste:=xlPasteFormulas

Many thanks Tim

Regards
John

 

[Guest]

Hi xLBaron
I tried your code as well as Tims and it worked a treat also
Many thanks
Regards
John

 

[xLBaron]

JohnUK -

May want to use something like this for multiple copy downs

Sub patches()

'Set the Data in Column B to be Range
Dim X As Range
Set X = Range("A1").Range(("B1"), Range("B1").End(xlDown))

'Copy Formula in "A1" down Column "A"
Range("a1").Copy
X.Offset(0, -1).Select
ActiveSheet.Paste


'Copy Formula in "F1" down Column "F"
Range("F1").Copy
X.Offset(0, 4).Select
ActiveSheet.Paste


'Copy Formula in "I1" down down Column "I"
Range("I1").Copy
X.Offset(0, 7).Select
ActiveSheet.Paste

Range("A1").Select
Application.CutCopyMode = False

End Sub

 

[Guest]

Hi xLBaron
Many thanks for that. I will definitely use this in future projects
Regards
John