To improve Efficiency of code, code running too long

[andywsw]

Hi guys,

i have this code to be run on excel. What i have done previously was spliting the email message body each text to each cells before running this code.

However every alternate cells has a blank cell for this particular sender( i have many sender with different formart, only this affecting) so by running this code will take out all the blank cells. i also notice that the blank cell contains Chr(10) .


This code is working but taking way too long to process for just one email only. So is there anyway to improve on this ? Timing for this was 30 sec ~ 50 Sec to process ( using timer ). About me i am new to VBA , those codes is i happen to learn through the net.

Thanks in an advance !

Range("F2").Select
Do Until IsEmpty(ActiveCell.Value)
If ActiveCell.Offset(0, -4).text = "abc@sss.com" Then
    Do Until IsEmpty(ActiveCell.Value)
    If ActiveCell.Value = Chr(10) Then
    Selection.Delete Shift:=xlToLeft
    End If
    ActiveCell.Offset(0, 1).Select
    Loop
End If
ActiveCell.Offset(1, 0).Select
Loop

[rylo]

Hi

If there is always something that will be to the far right on each of the rows to be actioned (ie past the last of the chr(10) items that are to be deleted) then try something like

Dim delrng As Range
  Set findit = Cells.Find(what:="abc@sss.com")
  Set delrng = Nothing
  If Not findit Is Nothing Then
    firstadd = findit.Address
    Do
      Set delrng = Nothing
      For Each ce In Range(findit, Cells(findit.Row, Columns.Count).End(xlToLeft))
        If ce = Chr(10) Then
          If delrng Is Nothing Then
            Set delrng = ce
          Else
            Set delrng = Union(delrng, ce)
          End If
        End If
      Next ce
    
      delrng.Delete shift:=xlToLeft
      Set findit = Cells.Find(what:="abc@sss.com", after:=findit)
    Loop Until findit.Address = firstadd
  End If

HTH

rylo

[andywsw]

Hi rlyo


thanks for the response !did try your codes.

It works! but it takes jsut as long as the original i been using

But i am going to use ur code seems more stable ( just the feeling ) !

i think there is not much way to make it faster i assume

Cheers

[rylo]

Hi

Don't give up quite so easily.

What would happen if you did a replace on the chr(10) items and made them blank? Sure the blank cells are still there, but can you just not process them?

Maybe give us an example of the file, and explain what it is you want to get from it.

rylo

[andywsw]

try.xlsx

this a sample of a file

[rylo]

and you want the result to be what? Or the processing to be?

Are you merely trying to make sure that all the data is in the first 3 columns?

What are you wanting to do?

rylo