need to copy row above but different thoughtout data


M

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
 
Ad

Advertisements

D

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
 
T

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
 
B

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
 
T

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



Bernie Deitrick said:
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
 
M

Michael Wise

Don said:
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
 
Ad

Advertisements

T

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

Michael Wise > said:
Don said:
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

 
M

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
 
Ad

Advertisements

T

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
 

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