PDA

View Full Version : [SOLVED] Filling in Empty Rows with whats in the row above --HELP



Fracture
04-07-2005, 10:10 AM
Hi,

I'm new to all of this (This site and VBA in Excel). Em basically i have a problem that i'm unable to solve.. so this is a cry for help.

My problem is this. i have a number of excel spreadsheets with somewhere in the region of 3000 entries. some of which are just blank rows. I need to fill these blank rows with whats in the row above them.

I have tried to modify some code i found in these very forums. (see below)


Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error GoTo Exits:
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), _
Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw - 1).EntireRow.Copy
Rng.Rows(Rw).EntireRow.Paste
RwCnt = RwCnt + 1
End If
Next Rw
Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

The Idea being that if i find an empty row, all i need to do is go back one
copy that row then move forward again and paste it in..

But it doesn't work.. plz help, i think im out of my depth..

Thanks for any and all help : pray2:

Ken Puls
04-07-2005, 11:19 AM
Hi Fracture, and welcome to VBAX!

Give this a shot:


Sub CopyRowAboveToBlankRows()
Dim Rw As Long, Rng As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
On Error GoTo Exits:
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
For Rw = 1 To Rng.Rows.Count
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw - 1).EntireRow.Copy Rng.Rows(Rw).EntireRow
End If
Next Rw
Exits:
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

Also, a few suggestions for you...
-Since your code doesn't actually delete rows, I've changed the name. It makes your code a little more self-documenting in a project. ;)
-I added a With block to your application calls. Since they're right together, it's good programming practice to do so, and will make the code a little faster since Excel doesn't have to re-read it's roadmap to get to the object you want to change each time. Time difference is negligible, but it's still a good practice to get into. :yes
-I changed your loop to start at the top and work down. This way, you don't miss any rows. The one you had was the right way if you do want to delete the rows, since the row numbers change, but for this problem copying, top-down is best.
-I've knocked out a variable or two in there as well.

Hope it helps!

Killian
04-07-2005, 11:43 AM
I had a (very) slightly different approach which I thought shouldn't go to waste


Sub FillEmptyRows()
Dim r As Long
Dim rng As Range
Application.ScreenUpdating = False
'set the range from rows 1 to the last used cell
Set rng = Range(Rows(1), Rows(ActiveSheet.Cells _
.SpecialCells(xlCellTypeLastCell).Row()))
For r = 2 To rng.Count 'for each row (skip the frst row)
'if the row is empty (CountA counts the number of cell that are not empty)
If Application.WorksheetFunction.CountA(ActiveSheet.Rows(r).EntireRow) = 0 Then
'set the range to current row
Set rng = ActiveSheet.Rows(r)
' make the current range valur equal the previous
rng.Value = ActiveSheet.Rows(r - 1).Value
End If
Next r
End Sub

brettdj
04-07-2005, 04:21 PM
or make your selection and run this



Sub FillBlanks()
Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub

Anne Troy
04-07-2005, 06:30 PM
I have a different approach, too, provided the cells are truly blank.
Select the cells in ONE column beginning with the first blank row and all the way down to the last row.
Hit Edit-Go to-Special-Blanks.

Assuming cell A4 is your first blank cell in column A, and you want to fill it with the contents of the cell above it (A3), type =A3 and hit Ctrl+Enter.

brettdj
04-07-2005, 08:03 PM
thats the non VBA approach of my one liner :)

Fracture
04-08-2005, 01:47 AM
Thanks Everyone!

I am using kpuls suggestion atm, but thanks to all of you for your ideas, you've all been an amazing help.

Cheers :thumb

Fracture.

Aaron Blood
04-08-2005, 05:41 AM
Tried to post it yesterday, but the board was locking up...
I see someone has come up with part of it.

Anyway, here's 3 little macros to ponder.


'Quick FORMULA fill...


Sub Macro1()
Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
End Sub
'Quick VALUE fill if the existing cells DO NOT contain formulas...
Sub Macro2()
Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Selection.Value = Selection.Value
End Sub

'Quick VALUE fill if the existing cells DO contain formulas...


Sub Macro3()
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
For Each area In Selection.Areas
area.Value = area.Value
Next area
End Sub

Oh and let the VBA tag guy know that his code is improperly splitting the FormulaR1C1 property (see above).

Ken Puls
04-08-2005, 09:30 AM
Hey everyone!

What a perfect example of the incredible amount of ways to "skin a cat"! That's one of the great things that I love about this place. :yes

Thanks to all of you guys who posted after I did. :thumb

Zack Barresse
04-08-2005, 09:35 AM
Fwiw, since we're all throwing around our opinions .. ( ;) ) Dreamboat's method will be 1) the quickest, 2) the most efficient and 3) the most user friendly. Besides it's the way I do this type of task(s), so I need to endorse it! :p

Ken Puls
04-08-2005, 09:51 AM
... 3) the most user friendly.

Until a user comes along who want to change 1 cell in the array, but doesn't know anything about arrays. ;) I learned about arrays the hard way myself. (Trying to do exactly that, and having no idea what to do!)

If you need someone else to be able to maintain the solution you develop for them, this could be a concern.

Zack Barresse
04-08-2005, 09:59 AM
I think you're thinking about confirming with Ctrl + Shift + Enter, not just Ctrl + Enter, which is a multi-enter command, not an array enter command.

Ken Puls
04-08-2005, 10:17 AM
:doh:

Yep. Stand corrected! Thanks, Zack!

Zack Barresse
04-08-2005, 10:17 AM
I'm here all week.