Solved: Used range code

[farrukh]

Hi All,

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?

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


Thanks and Regards
hammeed

[Zack Barresse]

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?

[farrukh]

Hi Zack Barresse,

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.

Thanks
Hammeed

[Zack Barresse]

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?

[farrukh]

Hi Zack Barresse,

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.

Sorry if i cannot understand it properly.

Thanks
Hammeed

[Zack Barresse]

Gotcha! No loop required, one simple line (with error handling if no blanks found)...
Sub ClearEM()
On Error Resume Next
Range("EL:EL").SpecialCells(xlCellTypeBlanks).Offset(0, 1).ClearContents
On Error GoTo 0
End Sub
HTH

[farrukh]

Hi Zack,

Thank you so much for your time, this is exactly i need too.

Thanks
hammeed

[Zack Barresse]

Great! Glad to help. :)

Can you please mark your thread as Solved, by clicking on the Thread Tools at the top?