Conditional Delete Cells and shift to the left

Hi,

I have an input data like that:
--A-B-C-D-E-F-G
1-5-7-7-7-5-7-5
2-7-5-5-5-7-5-7
3-5-5-5-7-7-5-5
and so on...

an output data I need:
--A-B-C-D-E-F-G
1-7-7-7-5-7-5
2-7-5-5-5-7-5-7
3-7-7-5-5
and so on...

as above, macro must to delete number 5 from left to right until not contain other number, and shift to left

my problem is similar to below:

Sub Account_or_something()
'1. http://www.mrexcel.com/forum/excel-questions/545536-conditional-delete-cells-right.html
'2. other codes here: http://www.ozgrid.com/forum/showthread.php?t=160748
'searching by google fraze: "end sub" "End(xlToLeft)" "until" "delete cells"

Do While [a1] <> "5"
        [a1].Delete shift:=xlToLeft
    Loop
End Sub

above macro works good, but only for one row...

any help?

ps. I have also other variant my data, like this:

input data:
--A-B-C-D-E-F-G
1-5-6-3-8-4-1-5
2-9-5-5-5-6-5-44
3-5-5-5-6-4-5-5
and so on...

output data:
--A-B-C-D-E-F-G
1-6-3-8-4-1-5
2-9-5-5-5-6-5-44
3-6-4-5-5
and so on...

I'm not clear... Do you want to delete all 5's?

Hi, markos97,

maybe like this:

Sub EF934924()
Dim lngCounter As Long

Const cstrCOL As String = "A"
Const clngNUM As Long = 5

For lngCounter = 1 To Cells(Rows.Count, cstrCOL).End(xlUp).Row
  Do While Cells(lngCounter, cstrCOL).Value = clngNUM
    Cells(lngCounter, cstrCOL).Delete shift:=xlToLeft
  Loop
Next lngCounter
End Sub

Ciao,
Holger

Hi, mahaveersomani,

your code will delete every occurrance in the sheet, not only those at the beginning of each row until any other number is found.

Sub autohide()

Application.ScreenUpdating = False
Cells.Replace What:="5", Replacement:="", LookAt:=xlWhole, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
Application.ScreenUpdating = True
End Sub

Ciao,
Holger

Yeah - That's it!
Macro works perfectly for me!
Thanks a lot HAHOBE, AndyLitch also many thanks!
and you - mahaveersomani (yes I remember) - ps, your code not exactly do what I need (save blank space between cells)

Hi, mahaveersomani,

using your approach the code may look like

Sub Delete5()

Application.ScreenUpdating = False

Do While Err = 0
  With Range("A:A")
    .Replace What:="5", Replacement:="", LookAt:=xlWhole, SearchOrder _
          :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    On Error Resume Next
    .SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
  End With
Loop

Application.ScreenUpdating = True

End Sub

Ciao,
Holger

one again, thank you HaHoBe, now your code looks like:

Sub EF934924()

Dim lngCounter As Long
Application.ScreenUpdating = False 'added

Const cstrCOL As String = "A"
Const clngNUM As Long = 55

For lngCounter = 1 To Cells(Rows.Count, cstrCOL).End(xlUp).Row
  Do While Cells(lngCounter, cstrCOL).Value = clngNUM
    Cells(lngCounter, cstrCOL).Delete Shift:=xlToLeft
    
  Loop
Next lngCounter
Application.ScreenUpdating = True 'added

End Sub

I need change this part of code

.Delete Shift:=xlToLeft

to something like that:

.ClearContents

but it does not work like I wanted;
I need only clear this cells (not shift to left in this moment), I'm try it, but the cells go to up...

--edit--

as above my input data looks like

--A-B-C-D-E-F-G
1- -7-7-7-5-7-5
2-7-5-5-5-7-5-7
3- - - -7-7-5-5

Hi, markos97,

sory but a sample workbook with what it looks like now and what it should look like after the macro would make things clearer. What should be cleared in Column A/B/what cells? The new sample isn´t explaining itself, and there is not 5 or 55 in the fist column as far as I can judge.

Ciao,
Holger

Hi, markos97,

to my opinion this is a different problem to what you originally asked help for.

Code for a standard module:

Sub EFEF934924_2()

Const cstrValue As String = "99"

Range("A4:AD" & Range("A" & Rows.Count).End(xlUp).Row).Replace What:=cstrValue, Replacement:="", LookAt:=xlWhole, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub

Ciao,
Holger