I'm a VBA novice. I have a spreadsheet with 5 columns of data in columns A through E with any number of rows (usually thousands). The problem is that for some of the rows the data is shifted to the right one column (in columns B through F). I wrote a macro to shift the rows that were off; the macro evaluates each row and if column A is empty, it cuts the 5 cells to the right and pastes them and then moves to the next row, if not empty it just moves to next row. What I came up with works, but takes way too long and heats up my laptop. I've searched ways to make the code more efficient, but have not come up with something that does the trick.
I'm requesting two things. First, given the code I have (the process of checking each row one by one), is there something I can do to make it run faster? Even if there is a better way to approach this, I'd love to learn how to make something like this faster if that is possible.
Second, I know there has to be a better way to approach this. Any suggestions will help me learn how to do things better.
Thanks in advance.
Doug
Sub Shift()
Application.ScreenUpdating = False
Dim RowCount As Integer
Dim lRow As Long
'Finds the last non-blank cell on a sheet/range.
lRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
' move cursor to cell A1
Range("A1").Select
ActiveCell.Offset(1, 0).Select
For RowCount = 2 To lRow
If ActiveCell.Value = "" Then
ActiveCell.Offset(, 1).Resize(1, 5).Cut
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next RowCount
Application.ScreenUpdating = True
End Sub
Bookmarks