autofill without a destination range

P

Peruanos72

Hello all,

i'm trying to autofill without specifying a destination range. is this
possible? i have data in a column seperated by blank cells. i'm looking to
autofill with one cell and then moving down to the next cell with data and
autofilling again. this process repeats until there is no more data to
autofill. note: the destination ranges change daily.

thanks in advance
 
R

ryguy7272

Here ya go:

Sub InsertCol()

Dim lastrow As Long


lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lastrow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

End Sub

Notice, this fills in gaps, based on data in ColumnA. If you want other
columns to be populated, add code just like the above example. If you want
to fill data in one column, based on the last row in another column, you can
do that too; the code is slightly different for that scenario.


Remember to make a back of your Excel file, just in case the result
is...ummmm...unintended. It's a total PITA to try to recover lost data.

Regards,
Ryan---
 
P

Peruanos72

thanks. it works perfectly.

is there a way to delete rows that have no data. with your code i'll need to
get rid of those rows first before filling in the blank cells. if i delete
them after i use your code then the formulas for the rows i need to keep no
longer show the correct data.

ryguy7272 said:
Here ya go:

Sub InsertCol()

Dim lastrow As Long


lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lastrow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

End Sub

Notice, this fills in gaps, based on data in ColumnA. If you want other
columns to be populated, add code just like the above example. If you want
to fill data in one column, based on the last row in another column, you can
do that too; the code is slightly different for that scenario.


Remember to make a back of your Excel file, just in case the result
is...ummmm...unintended. It's a total PITA to try to recover lost data.

Regards,
Ryan---

--
RyGuy


Peruanos72 said:
Hello all,

i'm trying to autofill without specifying a destination range. is this
possible? i have data in a column seperated by blank cells. i'm looking to
autofill with one cell and then moving down to the next cell with data and
autofilling again. this process repeats until there is no more data to
autofill. note: the destination ranges change daily.

thanks in advance
 
F

farid2001

Hola

This might work to delete empty rows:

Sub DeleteEmptyRows()
Dim LastRow As Long, r As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1

Application.ScreenUpdating = False

For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

Saludos
farid2001(de Peru)
 
P

Peruanos72

Gracias compadre. Soy un gringo pero mi esposa (mi media naranja) es de Peru.
Yo amo a Peru. Es un pais bien hermosa.

Y el codigo salio perfecto.

Suludos
 
P

Peruanos72

Ryan,

I've been helped with regard to deleting rows. Thanks again!!

ryguy7272 said:
Here ya go:

Sub InsertCol()

Dim lastrow As Long


lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A2:A" & lastrow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

End Sub

Notice, this fills in gaps, based on data in ColumnA. If you want other
columns to be populated, add code just like the above example. If you want
to fill data in one column, based on the last row in another column, you can
do that too; the code is slightly different for that scenario.


Remember to make a back of your Excel file, just in case the result
is...ummmm...unintended. It's a total PITA to try to recover lost data.

Regards,
Ryan---

--
RyGuy


Peruanos72 said:
Hello all,

i'm trying to autofill without specifying a destination range. is this
possible? i have data in a column seperated by blank cells. i'm looking to
autofill with one cell and then moving down to the next cell with data and
autofilling again. this process repeats until there is no more data to
autofill. note: the destination ranges change daily.

thanks in advance
 
R

ryguy7272

If everything worked out for you, Peruanos72, how about giving us some little
green checks. Normally, the positive feedback is like 1 in 10; for me it
seems to be more like 1 in 20.

Thanks,
Ryan---
 
P

Peruanos72

definitely. little green checks are on their way. i read up about the
importance of rating posts as helpful/unhelpful. i'll be sure to do this
going forward... thanks again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top