Hi I am trying to do a copy paste macro for a spread sheet a data
import that skips over a few fields. The spreadsheet is about 650 rows
so going through it manually is very tedious. I want the macro to be
able to copy paste when column "A" is blank the date from the row above
in columns A,B & D, E, F. It would look like this:

A B C D E F
as ad af ag ah ak
IT LO

After macro is run - seeing that column A is blank it would copy paste
from above to looke like this - Notice it copys over the previous
contents of column D and leaves column C alone.

A B C D E F
as ad af ag ah ak
as ad IT ag ah ak

Any ideas on how I could get this to work?

Thanks alot!

-Brian

Try this, just requires that the sheet with your data on it be 'selected'
when you run it. Prsumes your data starts in row 1 but you can change that
by changing the value of Const FirstDataRow.

Sub FillTheBlanks()
Const FirstDataRow = 1 ' change if needed
Dim LastDataRow As Long 'last row in C with data in it
Dim LC As Long ' loop counter
LastDataRow = Range("C" & Rows.Count).End(xlUp).Row
If LastDataRow < (FirstDataRow + 1) Then
Exit Sub ' nothing to do
End If
Application.ScreenUpdating = False
For LC = FirstDataRow To LastDataRow
If IsEmpty(Range("A1").Offset(LC, 0)) And _
Not (IsEmpty(Range("A1").Offset(LC, 2))) Then
'copy column A down
Range("A1").Offset(LC, 0) = Range("A1").Offset(LC - 1, 0)
'copy column B down
Range("A1").Offset(LC, 1) = Range("A1").Offset(LC - 1, 1)
'copy column D down
Range("A1").Offset(LC, 3) = Range("A1").Offset(LC - 1, 3)
'copy column E down
Range("A1").Offset(LC, 4) = Range("A1").Offset(LC - 1, 4)
'copy column F down
Range("A1").Offset(LC, 5) = Range("A1").Offset(LC - 1, 5)
End If
Next
Application.ScreenUpdating = False
End Sub

"Bongard" wrote:

Hi I am trying to do a copy paste macro for a spread sheet a data
import that skips over a few fields. The spreadsheet is about 650 rows
so going through it manually is very tedious. I want the macro to be
able to copy paste when column "A" is blank the date from the row above
in columns A,B & D, E, F. It would look like this:

A B C D E F
as ad af ag ah ak
IT LO

After macro is run - seeing that column A is blank it would copy paste
from above to looke like this - Notice it copys over the previous
contents of column D and leaves column C alone.

A B C D E F
as ad af ag ah ak
as ad IT ag ah ak

Any ideas on how I could get this to work?

Thanks alot!

-Brian

Awesome I got it to work! That will save me some huge headaches in the
future!

Thanks a lot !
brian


JLatham (removethis) wrote:
Try this, just requires that the sheet with your data on it be 'selected'
when you run it. Prsumes your data starts in row 1 but you can change that
by changing the value of Const FirstDataRow.

Sub FillTheBlanks()
Const FirstDataRow = 1 ' change if needed
Dim LastDataRow As Long 'last row in C with data in it
Dim LC As Long ' loop counter
LastDataRow = Range("C" & Rows.Count).End(xlUp).Row
If LastDataRow < (FirstDataRow + 1) Then
Exit Sub ' nothing to do
End If
Application.ScreenUpdating = False
For LC = FirstDataRow To LastDataRow
If IsEmpty(Range("A1").Offset(LC, 0)) And _
Not (IsEmpty(Range("A1").Offset(LC, 2))) Then
'copy column A down
Range("A1").Offset(LC, 0) = Range("A1").Offset(LC - 1, 0)
'copy column B down
Range("A1").Offset(LC, 1) = Range("A1").Offset(LC - 1, 1)
'copy column D down
Range("A1").Offset(LC, 3) = Range("A1").Offset(LC - 1, 3)
'copy column E down
Range("A1").Offset(LC, 4) = Range("A1").Offset(LC - 1, 4)
'copy column F down
Range("A1").Offset(LC, 5) = Range("A1").Offset(LC - 1, 5)
End If
Next
Application.ScreenUpdating = False
End Sub

"Bongard" wrote:

Hi I am trying to do a copy paste macro for a spread sheet a data
import that skips over a few fields. The spreadsheet is about 650 rows
so going through it manually is very tedious. I want the macro to be
able to copy paste when column "A" is blank the date from the row above
in columns A,B & D, E, F. It would look like this:

A B C D E F
as ad af ag ah ak
IT LO

After macro is run - seeing that column A is blank it would copy paste
from above to looke like this - Notice it copys over the previous
contents of column D and leaves column C alone.

A B C D E F
as ad af ag ah ak
as ad IT ag ah ak

Any ideas on how I could get this to work?

Thanks alot!

-Brian

Thanks a lot this will save me some huge headaches in the future!!

-brian