Macros with unknown table lengths

  • Thread starter Thread starter mstorkamp
  • Start date Start date
M

mstorkamp

Hi,
I'm relatively new to macro programming with excel, and I'm having
trouble dealing with variable length data. Every week I get a
spreadsheet that has data in columns A through J, and rows 1 through
somewhere around 3000 plus or minus a hundred or so, and 3 sheets. I
need to take the data from sheets 2 and 3 and append them to the
bottom of sheet 1, as well as put a formula into column K and copy it
down to the end of the data. When I tried to record a macro doing
this, it always saves a cell address for the last row in this
particular spreadsheet, so it won't work on next weeks because there
will be a different number of rows.

Is there an easy way to fill column K down to the same number of
rows for which there is data in column A?
 
You should always post YOUR coding efforts for comments and suggestions.
maybe these will help.
slr=sheets(sourcesheetname).cells(rows.count,"a").end(xlup).row
dlr=sheets(destinationsheetname).cells(rows.count,"a").end(xlup).row+1
 
Is there an easy way to fill column K down to the same number of
You should always post YOUR coding efforts for comments and suggestions.
maybe these will help.
slr=sheets(sourcesheetname).cells(rows.count,"a").end(xlup).row
dlr=sheets(destinationsheetname).cells(rows.count,"a").end(xlup).row+1
Click to expand...

Ok, here's what got recorded in the macro:
Range("K2:K3145").Select
Selection.FillDown

The problem is, in the next spreadsheet I will need to have a
different number of rows. Can I replace K3145 with a variable, and how
do I get the value 'K3145' into that variable (where 3145 is the last
row with data)?
 
How do you know that 3145 is the last row with data? Can you look at another
column to see the last used row?

dim LastRow as long
with Activesheet
'I used column A to get that last used row
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("K2:K" & lastrow).filldown
end with
 
try
Range("K2:K" & cells(rows.count,"k").end(xlup).row).FillDown
Click to expand...

Thanks Don and Dave, that's what I needed to get it to work (I know, I
still have a lot of the basics to learn, but I needed to get past this
one problem quickly).
 
Back
Top