fill a variable sized range

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have data elements in cols AC:AF. Data will be in rows from 5 to whatever.
I need to add a value from another named cell in the worksheet into col AB.
How do I do it so I only add values in AB that have data in AC?
 
Have you tried using:

If activecell.offset(0,1).Value<>"" then

Where activecell is the cell in column AB?
 
Your question is not clear to me. Initially you say you want to add a value
(singular) from a named cell (do you mean a cell with a Defined Name, or do
you just mean you want to specify the cell address?) but then you go on to
say you want to add values (plural) in AB that have data in AC... is there
any linkage between what you want to put in AB and what is in AC, or do you
just want to put the value(s?) in the first blank cell in AB where AC next
to it is not blank? I guess my main problem is I can't visualize your setup
from your description.
 
I can use that but if I have that formula it create blank cells which i don't
want. If I have 5 active cells in AC I only what 5 values in AB so my
database doesn't have empty rows.
 
I am using a macro to extract records from a database into columns AC:AF.
The number of records change based on criteria selection. I then need to add
another value (Defined Range Name) to each row in AB. The value will be the
same and is stored elsewhere in the worksheet. If AC:AF has 5 rows I only
want to put the AB value on those same 5 rows, if AC:AF has 10 rows I want to
add it to those 10 rows. So how to I control the macro to add the value in
AB for just those rows with a value in AC.

I hope that helps
 
Okay, I think I see what you want. Give this macro a try...

Sub FillColumnAB()
Dim X As Long, LastRow As Long
Const StartRow = 2 '<<==assumes Row 1 is a header row
LastRow = Cells(Rows.Count, "AC").End(xlUp).Row
Range("DefinedName").Copy Range("AB" & StartRow & ":AB" & LastRow)
End Sub

Change my example "DefinedName" to your own defined range name (make sure to
keep the quotes); change the StartRow constant if need as well.
 
Back
Top