How to fill blank cells with values in other column?

[rizwanulhasan]

Hi all,

Hope all going good.

In attached file, there's a macro in module 4 whose step 2 doesn't work properly.

My requirement is that if there are blank cells in column J (upto last row of column A), then those blank cells should be filled with the corresponding row value of column Q. But macro still gives blanks.

Can someone please check what's wrong with the code.

Thanks in advance.

[Marc L]

Hi,

'cause the cells are not 'blank' like you can check yourself with an easy Excel formula …

[rizwanulhasan]

@Marc L

Thanks for your response Marc.

Sorry i forgot to mention the initial steps.

The sheet "SAP Data" will be cleared and the the entire data form sheet "Raw Data" will be pasted in sheet "SAP Data" post which macro will run.

Columns before running, macro will be J / Q and post macro will become I / P.

Could you please help with modifying the code such that the cells that appear blank in Column J (upto data till last row in column A) should be filled with the corresponding row values of column Q.

Thank you.

[Marc L]

From where comes the Raw Data worksheet data ?!

[rizwanulhasan]

The data in sheet "Raw Data" will be downloaded from external source and directly pasted in sheet "SAP Data".

I have given the sheet "Raw Data" just for reference. In actual, the sheet wont exist.

[bebo021999]

Because the cells in column J appear to be empty, but there is actually a space.
Therefore, the condition for J is either empty or contains a space

' Step 2: Fill blank cells in column J with values from column Q 
    Dim LastRow As Long 
    LastRow = Cells(Rows.Count, "J").End(xlUp).Row 
    For i = 2 To LastRow 
        If IsEmpty(Cells(i, "J")) Or AscW(Cells(i, "J")) = 32 Then ' add 1 more condition here 
            Cells(i, "J").Value = Cells(i, "Q").Value 
        End If 
    Next i  

[Marc L]

The data in sheet "Raw Data" will be downloaded from external source and directly pasted in sheet "SAP Data".

So seems to be badly imported !
As the obvious efficient is to correct the import in order there is nothing to do 'after' …
Or why don't you just copy directly column Q to column J ? Just with a single codeline !

[rizwanulhasan]

@bebo

Thanks for the help. But post running the code, it still shows blank.

@Marc

No Marc, cant copy entire column Q to J because some values are different between both columns and therefore only blank cells in column J needs to be filled.

[Marc L]

It's what happens with bad attachment not well reflecting the real workbook, what could you expect proceeding like this ? …

[rizwanulhasan]

Guess this is working

Please Login or Register  to view this content.

[Marc L]

I guess so, just hoping the worksheet as few data rows … Can be achieved without any loop just with an Excel basics formula …

[rizwanulhasan]

It's what happens with bad attachment not well reflecting the real workbook, what could you expect proceeding like this ? …

Agree Marc. But that's how the extract is downloaded. I do not have any option to change it

[rizwanulhasan]

Thanks all for your help. Greatly appreciated

[Marc L]

(removed as my bad)

[bebo021999]

@bebo
Thanks for the help. But post running the code, it still shows blank.

It works for me
BTW, your code should be shorter, like this:

Sub FormatAndCleanData() 
    ' Activate the "SAP Data" sheet from the "Main_Page" 
    Sheets("SAP Data").Activate 
     
    ' Step 1: Format column "A" to system date 
    Columns("A").NumberFormat = "dd-mm-yyyy" 
     
    ' Step 2: Fill blank cells in column J with values from column Q 
    Dim LastRow As Long 
    LastRow = Cells(Rows.Count, "J").End(xlUp).Row 
    For i = 2 To LastRow 
        If IsEmpty(Cells(i, "J")) Or AscW(Cells(i, "J")) = 32 Then 
            Cells(i, "J").Value = Cells(i, "Q").Value 
        End If 
    Next i 
     
    ' Step 3: Delete columns "B", "AA", and "AD" 
    Range("B1,AA1,AD1").EntireColumn.Delete 
     
    ' Step 4: Center align all columns 
    Cells.HorizontalAlignment = xlCenter 
     
    ' Step 5: Fit width of all columns except column "U" 
     ActiveSheet.UsedRange.Columns.AutoFit 
     Columns("U").ColumnWidth = Columns("XFD").ColumnWidth ' set defaul width 
     
    ' Step 6: Go to cell A1 
    Range("A1").Select 
     
    ' Step 7: Go to sheet "Main_Page" and give a pop-up message 
    Sheets("Main_Page").Activate 
    MsgBox "Done" 
End Sub