Thread: Range to increase/decrease with data

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

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


"JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
news:35BF4DFA-C089-4AFB-B2CD-973E2AE60CDA@microsoft.com...
> 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
>

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

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
news:OsQDE2slGHA.4076@TK2MSFTNGP03.phx.gbl...
> 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
>
>
> "JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
> news:35BF4DFA-C089-4AFB-B2CD-973E2AE60CDA@microsoft.com...
>> 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
>>
>
>

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

"Tim Marsh" wrote:

> 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
>
>
> "JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
> news:35BF4DFA-C089-4AFB-B2CD-973E2AE60CDA@microsoft.com...
> > 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
> >
>
>
>

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




"xLBaron" wrote:

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

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

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

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