autofill without a destination range

[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

 

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

 

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

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

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

 

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

 

[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

 

[Peruanos72]

Ryan,

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

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

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

 

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

 

[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