ifs have things in common, so you can restructure it
Public Sub FixAbstracts()
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).row
Dim i As Long
Dim rowCount As Long
Dim isEmptyCell As Boolean
For i = lastRow To 2 Step -1
isEmptyCell = IsEmpty(Cells(i, 1))
If isEmptyCell Then
rowCount = Application.CountA(Rows(i))
If rowCount > 0 Then Cells(i, 1).Delete xlshift:=xlToLeft
If rowCount = 0 Then Rows(i).Delete
ElseIf Not IsNumeric(Cells(i, 1)) Then
Cells(i - 1, 13) = Cells(i, 1)
Cells(i, 1).Delete xlshift:=xlToLeft
Now you have a
For loop so no need to increment
It also steps backwards bottom to top so nothing is skipped. If you delete row(10) and go to the next row, the original row(11) is skipped. That's a result of the
For loop, but it prefer it much to a
Is it empty?
We also declared a
boolean to check for empty so it's only done once per loop. Now we base our
if on that to reduce multiple checks.
So now the logic is
- empty? Yes? Count the row. Now do something
- empty? No? is it a number by itself? Yes? Do something
Is it a number?
Let's look at the logic of yours
- is it a number? yes? goto next iteration
- is it a number? no? is it empty? yes? Do something
- is it empty? yes? count the values in the row = 0 then do something
- is it empty? yes? count the values in the row > 0 then do something
See the reduction? We get rid of the initial
isnumeric because the loop will iterate by itself. We also check for empty only once and we only count once.
Not to mention your code is very difficult to follow.
If Application.WorksheetFunction.IsNumber(Rng.Value) = True Then
i = i + 1
ElseIf IsNumeric(Rng.Value) = False And Application.WorksheetFunction.CountA(Rng) = 1 Then
Rng.Copy Destination:=Rng.Offset(-1, 13)
ElseIf IsEmpty(Rng) = True And Application.CountA(row) > 0 Then
ElseIf IsEmpty(Rng) = True And Application.CountA(row) = 0 Then
I had to reread this a bunch of times and I'm still not sure if I'm doing all the checking you are. You aren't consistent with how you're checking for a numeric value, you use a worksheet function and the vba function, there's probably no need to use the worksheet function if your data is standardized.
There's also no need to check if something like
isempty = true as the result of
isempty is either