how can i place blank on entire column value contains "00-01-1900" through VBA

[m_vishal_c]

HI. I want to place blank (" ") on entire column . there are so many "00-01-1900" values . so i want to replace as blank (" "). is it possible by VBA.

Please guide me

heaps thanks in advance

 

[mrshl9898]

Hi,

This will replace "00-01-1900" with ""

Just change the column number to the column you need to replace in.

Sub Replace()

Dim rownum As Long
Dim colnum As Long


rownum = 1
colnum = 1 'change to reflect column you are changing 1=A, 2=B etc


Do Until rownum = ActiveSheet.UsedRange.Rows.Count + 1
    Cells(rownum, colnum).Replace What:="00-00-1900", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    rownum = rownum + 1
Loop


End Sub

 

[m_vishal_c]

Hi,

This will replace "00-01-1900" with ""

Just change the column number to the column you need to replace in.

Sub Replace()

Dim rownum As Long
Dim colnum As Long


rownum = 1
colnum = 1 'change to reflect column you are changing 1=A, 2=B etc


Do Until rownum = ActiveSheet.UsedRange.Rows.Count + 1
    Cells(rownum, colnum).Replace What:="00-00-1900", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    rownum = rownum + 1
Loop


End Sub

sorry , it does not work

 

[mrshl9898]

Sorry, just replace "00-00-1900" with "00-01-1900" in my above

 

[m_vishal_c]

Sorry, just replace "00-00-1900" with "00-01-1900" in my above

hi, i already replaced. but still not affecting

 

[mrshl9898]

That's odd. Maybe a version issue as I have no problems with it.

Does this work?

Sub Replace()

    Cells.Replace What:="00-01-1900", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False




End Sub

 

[m_vishal_c]

That's odd. Maybe a version issue as I have no problems with it.

Does this work?

Sub Replace()

    Cells.Replace What:="00-01-1900", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False




End Sub

does it count all entire workbook or particular column. but need to assign column count no.

 

[mrshl9898]

Just the active sheet. Wanted to try and work out where the other code is failing.

If it replaces all in the sheet the other code should work if the correct column is being inputted.

This is out of my depth if that is not the case i'm afraid.

 

[m_vishal_c]

hi i don't mind if it changes active sheet. but still it does not get changed, still it shows 00-01-1900 value in cell.

 

[RoryA]

Please take a minute to read the forum rules, especially related to cross-posting, and then comply with them. Thanks.