I want to change this code when ever i run this code it takes a lot of time to run because of range, please modify this code For each K of used range of El:El column?
[VBA]Sub Test()
Dim k As Range
For Each k In Range("EL:EL")
If IsEmpty(k) Then
Range("Em:em").ClearContents
End If
Next
End Sub[/VBA]
I wonder if the logic is what you want. The code doesn't make sense. You're looping through every cell in column EL, but in any empty cell you'll clear the contents of column EM. Why not just do it in one fell swoop if that is the case? Perhaps you can explain a little more what it is you are trying to do?
I am using this code if (EL1:EL2) Range is empty then clear the (EM1:EM2) it works for me , i need to use it for specific column when i define this whole column (EL:EL) then code search the whole cells of the EL column then clear EM column. I need that code just go for used range of the column and run the code to save a lot of time.
I understand the first part you're saying, Hammeed, although it's still a little confusing. Do you mean you want to search all of column EL and if there is no value in that cell then clear the adjacent cell in column EM? For example, if EL14 was blank, clear cell EM14? Or do you mean if you find any cell in a range in column EL (i.e. EL1:EL100), then clear column EM?
Yes your first ask according to my need
Like in attached workbook.
I need that every empty cell in EL Column then Clear the contents of EM , if find both Column EL and EM empty then then skip do not go for the last range of the columns.
Gotcha! No loop required, one simple line (with error handling if no blanks found)...
[vba]Sub ClearEM()
On Error Resume Next
Range("EL:EL").SpecialCells(xlCellTypeBlanks).Offset(0, 1).ClearContents
On Error GoTo 0
End Sub[/vba]
HTH
Solved: Used range code
Posting Permissions
Reply With Quote
