0

is there an efficient way of removing cells base on a cell next to it being empty?

Dim v As Integer
v = 1
For Each lol In Range("B6:B5006")

If lol.text = "" Then

Cells(v, 3).Value = ""
Cells(v, 4).Value = ""
Cells(v, 5).Value = ""
Cells(v, 6).Value = ""

End If
v = v + 1
Next lol

i want loop through a range of 6 to 5006 in column b and if it is empty then remove columns C,D,E,F which has worked for me before in smaller work but for some reason is this project when i run it max's the CPU out and will eventually crash (do not know why it does on an i3) so wondering if there was a more efficient way of doing this.

Jimjebus
  • 99
  • 6
  • 17
  • possible duplicate of [Efficient way to delete entire row if cell doesn't contain '@'](http://stackoverflow.com/questions/16901436/efficient-way-to-delete-entire-row-if-cell-doesnt-contain) –  Oct 02 '14 at 09:33

2 Answers2

1

It's not fundamentally about efficiency but more about the fact that assigning the .Value to "" does not clear the cell, but rather inserts a zero length string into that cell.

If you replace

If lol.text = "" Then

with

If VBA.IsEmpty(lol) Then

and

Cells(v, 3).Value = ""

with

Cells(v, 3).Clear

etc. then all will be well. Also, drop the row counter v and use lol.Row instead. You shouldn't really assume that the lols come back in any particular order when using For Each.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
0

Following code may work little bit faster than your code.

Dim v As Integer

For v=6 to 5006
    if cells(v,2)="" then
        Range("C" & v & ":F" & v).clear  ' Clear cells Cv:Fv
    End If
Next v

This code has some advantages in the view point of execution speed.

1)clears four cells at once

2)use simple for loop instead of foreach loop

Fumu 7
  • 1,091
  • 1
  • 7
  • 8
  • how is using a "simple for loop" instead of foreach loop on a collection an improvement? An Autofilter would have been an improvement... –  Oct 02 '14 at 09:31