need to copy row above but different thoughtout data

[Michael Wise]

I am needing to look through varying rows of data looking for empt
cells in col B-I. If blanks are found on that row in all colums B-I
need it to copy columns B-I right above it and paste it in that row
Once done it will need to keep on till it finds the next empty colum
sets. I hope this is descriptive enough.
Michae

 

[Don Guillett]

something like this (untested)
for i = cells(rows.count,"a").end(xlup).row to 2 step -1
if application.countif(range(cells(i,"b"),cells(i,"I"))=0 then
rows(i-1).copy rows(i)
end if
next i

 

[Tom Ogilvy]

Sub AAtester1()
lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = 2 To lastrow
If Application.CountA(Cells(i, 2).Resize(1, 9)) = 0 Then
Cells(i, 2).Resize(1, 9).FillDown
End If
Next
End Sub

 

[Bernie Deitrick]

Michael,

Perhaps doing them all at once?

Select your table, then use Edit | Go To... Special Blanks, press OK.
Then type and = sign, hit the up arrow key once, then press Ctrl-Enter.

That will fill all blanks with the values above them. You can copy
pastespecial values to change the formulas to values if you don't want the
formulas.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Just a not to Michael as I thought about this approach as well - but it does
not just work on rows where columns B to I are all empty. If just G10 were
empty in row 10, it would get filled as well.

If there is no situation like that and if one cell in those columns is blank
then all cells in those columns are blank than Bernies suggestion is the
fastest way to do it.

You would have to select the whole range to do the copy and pastespecial
values, so if that is a requirement, if other cells might have formulas,
this would overwrite them as well. Just a point to consider as well.

--
Regards,
Tom Ogilvy

Michael,

Perhaps doing them all at once?

Select your table, then use Edit | Go To... Special Blanks, press OK.
Then type and = sign, hit the up arrow key once, then press Ctrl-Enter.

That will fill all blanks with the values above them. You can copy
pastespecial values to change the formulas to values if you don't want the
formulas.

HTH,
Bernie
MS Excel MVP

 

[Michael Wise]

something like this (untested)
for i = cells(rows.count,"a").end(xlup).row to 2 step -1
if application.countif(range(cells(i,"b"),cells(i,"I"))=0 then
rows(i-1).copy rows(i)
end if
next i

Don,
On this one i'm getting an Invalid number of arguments. I did notic
a typo right after 'cells(i,"I"))' I had to add another ) I hope i
was the right spot.

Tom,
Don't know if I was missing something on yours but when it got t
this point in the script it did nothing. I'm new at this so could no
see where I could come up with anything to spark it.

Awaiting more food *smile* thanks for the help.
Michae

 

[Tom Ogilvy]

Code was tested and worked perfectly for me.

you statement about getting to this point in the script is meaningless.

Only problem I would be able to see would be if you had no rows that were
actually blank in columns B to I

Don's countif function continues to be broken since it doesn't have a second
argument and he is copying the entire row above, but maybe that is what you
wanted.


--
Regards,
Tom Ogilvy

something like this (untested)
for i = cells(rows.count,"a").end(xlup).row to 2 step -1
if application.countif(range(cells(i,"b"),cells(i,"I"))=0 then
rows(i-1).copy rows(i)
end if
next i

Don,
On this one i'm getting an Invalid number of arguments. I did notice
a typo right after 'cells(i,"I"))' I had to add another ) I hope it
was the right spot.

Tom,
Don't know if I was missing something on yours but when it got to
this point in the script it did nothing. I'm new at this so could not
see where I could come up with anything to spark it.

Awaiting more food *smile* thanks for the help.
Michael

 

[Michael Wise]

Tom,
To clarify, yes it will actually be the whole row up to col I that al
cells will be blank. I would appear at first something like this

P02 SDOSR STORE LPG 50492471 SRSTVEP-07 PIPING EXINSP 1-May-08

XXX XXXXX XXXXX XXX XXXXXXXX XXXXXXXXX XXXXXX VISIN1 1-Nov-05

the "X" here represent what cells would be blank for sake of example.
spaces here represent each cell break. I have existing data pretainin
to the same item right above it I need that copied. Although not ever
line will be blank. But where those lines are blank up to col. I th
information is needed below. And it is only where all col B-I are blan
this is needed

 

[Tom Ogilvy]

Then I miscounted the number of cells between B and I inclusive. the
adjustment would be:

Sub AAtester1()
lastrow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = 2 To lastrow
If Application.CountA(Cells(i, 2).Resize(1, 8)) = 0 Then
' copy down A to I
Cells(i, 1).Resize(1, 9).FillDown
End If
Next
End Sub