VBA TO Correct Date format

[malleshg24]

Hi Team,
I am new in VBA, please assist me in below code ,
I want to put LR (last Row) in colulmn array .
One Single LR for all columns and where to add that line in looping.
Also Can someone expalin me the line what it indicates : - Worksheets(SheetNames(X)).Columns(Cols(X)).Replace " *", "", xlPart

Sub Correct_DateFormat_ineach_sheets_Column()
Dim wbk As Workbook
Dim lr As Long
Set wbk = Workbooks.Open(Sheets("sheet1").Range("b5").Value)

lr = Cells(Rows.Count, 1).End(xlUp).Row

Dim X As Long, SheetNames As Variant, Cols As Variant
SheetNames = Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
Cols = Array("J2:J" & lr, "L2:L" & lr, "P2:P" & lr, "R2:R" & lr)
For X = LBound(SheetNames) To UBound(SheetNames)
Worksheets(SheetNames(X)).Columns(Cols(X)).Replace " *", "", xlPart
Worksheets(SheetNames(X)).Columns(Cols(X)).NumberFormat = "yyyy-mm-dd"
Next
End Sub

Thanks in Advance for your Precious time !!

Regards,
Mallesh

[mancubus]

you may be a vba newbie, but definitely not a vbaexpress newbie.
please use code tags when posting your code.

for replace and date format, you can use whole column.

Sub vbax_61213_correct_cate_format_sheets_cols()

    Dim wbk As Workbook
    Dim x As Long
    Dim SheetNames As Variant, Cols As Variant
    
    Set wbk = Workbooks.Open(Sheets("Sheet1").Range("B5").Value)
    
    SheetNames = Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
    Cols = Array("J:J", "L:L", "P:P", "R:R")
    
    With ActiveWorkbook
        For x = LBound(SheetNames) To UBound(SheetNames)
            .Worksheets(SheetNames(x)).Columns(Cols(x)).Replace " *", "", xlPart
            .Worksheets(SheetNames(x)).Columns(Cols(x)).NumberFormat = "yyyy-mm-dd"
        Next
    End With
    
End Sub

[mancubus]

.Replace " *", "", xlPart

partially replaces the stings which start with a space in cells' values with null string, ie removes them.

"ssss" becomes "ssss" (doe not change)
"ddd ff" becomes "dd"
"12 345" becomes "12"
" qwerty" becomes "" (first character is a space)
"qwerty " becomes "qwerty" (last character is a space)

[malleshg24]

Hi Mancubus,

Thanks for you quick reply and your information on .Replace " *", "", xlPart

However, I am still facing an issue, Subscript out of Range , for below line,

I would like to mention I have header in each column. I want the date format till lastrow.

.Worksheets(SheetNames(x)).Columns(Cols(x)).Replace " *", "", xlPart
.Worksheets(SheetNames(x)).Columns(Cols(x)).NumberFormat = "yyyy-mm-dd"

Please can someone assist.

Thanks in Advance
Mallesh

[mancubus]

post your workbook pls

[malleshg24]

Hi, Mancubus

The above code worked for me , When I put sheetName in array. Thanks a log !

Regards,
Mallesh