PDA

View Full Version : VBA Basics - automating the process of running commands down thousands of rows



nicksinthemi
04-04-2012, 05:52 AM
I have a non-automated solution for my problem, but to do this i need to copy two formulas down 3000 rows. Ca someone show me how to automate this process?

The problem is quite simple: in a two column table import from word to excel, sometimes the page breaks create extra rows. I get nothing in the left row and the content in the right is split over two cells. So far, this is my semi-VBA solution:

1. i use an if command to concatenate the right cell to the one above if left cell is blank

2. i use another if command to insert a # marker in column C if the left cell is blank

3. i run a VBA command that deletes any rows containing a #

Can someone explain how i automate the process of dragging a formula down thousands of rows or do I have to render it in offset or something like that... ???

Bob Phillips
04-04-2012, 06:13 AM
Off the top


Application.ScreenUpdating = False

With Activesheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = lastrow to 2 Step -1

If .Cells(i, "A").Value = "" then

.Cells(i-1, "B").Value = .Cells(i-1, "B").Value & " " & .Cells(i, "B").Value
.Rows(i).Delete
End If
End With

Application.ScreenUpdating = True
End Sub

nicksinthemi
04-04-2012, 06:22 AM
Wow. Does this basically merge my offending rows, collapsing my three dirty steps into one clever step?

nicksinthemi
04-04-2012, 06:45 AM
Im getting a complie error - 'cannot end with'

nicksinthemi
04-04-2012, 06:48 AM
Sorry:

'Compile Error:

End With without With'

nicksinthemi
04-04-2012, 06:52 AM
An End if is missing somewhere?

Bob Phillips
04-04-2012, 08:15 AM
It's a missing Next i


Application.ScreenUpdating = False

With Activesheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = lastrow To 2 Step -1

If .Cells(i, "A").Value = "" Then

.Cells(i-1, "B").Value = .Cells(i-1, "B").Value & " " & .Cells(i, "B").Value
.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub