Unable to find blank cells and skip it

[Adrian Low]

Hi, I am trying to detect blank cells in the column and delete the cell away such that my other values can copy to the rest of the next cell.

Here are the codes that I've tried:

Dim wsInput As Worksheet, wsOutput As Worksheet, LastRow As Long, C As Range, D As Range, A As Range, B As Range, V As Range, Z As Range


Set wsInput = Workbooks("InputB.xls").Worksheets("HC_MODULAR_BOARD_20180112")
Set wsOutput = Workbooks("Output.xls").Worksheets("Sheet1")
Set Ws2 = Workbooks("InputA.xls").Worksheets("Sheet0")


With wsInput


LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row

For Each C In .Range("F3:F" & LastRow)
If Len(C.Offset(0, 1).Value <> "") Then
wsOutput.Cells(C.Row, "I").Value = C & "" & C.Offset(0, 1)
Else
Rows(C).Delete
End If
Next C

 

[DanteAmor]

Try this.
Change data in red by your information.

Sub test_del()
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Dim LastRow As Long, i As Long
    
    Set wsInput = [COLOR=#ff0000]ThisWorkbook[/COLOR].Worksheets("HC_MODULAR_BOARD_20180112")
    Set wsOutput = Workbooks("[COLOR=#ff0000]Output.xlsx[/COLOR]").Worksheets("Sheet1")
    
    With wsInput
        LastRow = .Cells(.Rows.Count, "[COLOR=#ff0000]F[/COLOR]").End(xlUp).Row
        For i = LastRow To 2 Step -1
            If .Cells(i, "[COLOR=#ff0000]F[/COLOR]").Value <> "" Then
                wsOutput.Cells(i, "[COLOR=#ff0000]I[/COLOR]").Value = .Cells(i, "[COLOR=#ff0000]F[/COLOR]").Value & "" & .Cells(i, "[COLOR=#ff0000]G[/COLOR]").Value
            Else
                .Rows(i).Delete
            End If
        Next
    End With
    MsgBox "End"
End Sub

 

[Adrian Low]

Try this.
Change data in red by your information.

Sub test_del()
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Dim LastRow As Long, i As Long
    
    Set wsInput = [COLOR=#ff0000]ThisWorkbook[/COLOR].Worksheets("HC_MODULAR_BOARD_20180112")
    Set wsOutput = Workbooks("[COLOR=#ff0000]Output.xlsx[/COLOR]").Worksheets("Sheet1")
    
    With wsInput
        LastRow = .Cells(.Rows.Count, "[COLOR=#ff0000]F[/COLOR]").End(xlUp).Row
        For i = LastRow To 2 Step -1
            If .Cells(i, "[COLOR=#ff0000]F[/COLOR]").Value <> "" Then
                wsOutput.Cells(i, "[COLOR=#ff0000]I[/COLOR]").Value = .Cells(i, "[COLOR=#ff0000]F[/COLOR]").Value & "" & .Cells(i, "[COLOR=#ff0000]G[/COLOR]").Value
            Else
                .Rows(i).Delete
            End If
        Next
    End With
    MsgBox "End"
End Sub

Hi it still does not work.

Set wsInput = Workbooks("InputB.xls").Worksheets("HC_MODULAR_BOARD_20180112")
Set wsOutput = Workbooks("Output.xls").Worksheets("Sheet1")
Set Ws2 = Workbooks("InputA.xls").Worksheets("Sheet0")


With wsInput

LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For i = LastRow To 2 Step -1
If .Cells(i, "F").Value <> "" Then
wsOutput.Cells(i, "I").Value = .Cells(i, "F").Value & "" & .Cells(i, "G").Value
Else
.Rows(i).Delete
End If
Next

https://imgur.com/V4ulqeg
The image of the output ^^

 

[DanteAmor]

Can you explain what does not work?
Explain what you need with your words.
If the row in column "F" is = "" you want to delete the row?
In the cells of column "F" do you have formulas ?, check that you really have "", maybe you can have blank spaces " "

 

[Adrian Low]

Can you explain what does not work?
Explain what you need with your words.
If the row in column "F" is = "" you want to delete the row?
In the cells of column "F" do you have formulas ?, check that you really have "", maybe you can have blank spaces " "

As you can see from the link (the output of my marco) the column "I" there are some blanks over there I need it to be deleted and shift the rest of the values upwards.

It is plain data with no formula

 

[DanteAmor]

Do you want to check column I or column F?
Try

Sub test_del()
    Dim wsInput As Worksheet, wsOutput As Worksheet
    Dim LastRow As Long, i As Long
    
    Set wsInput = ThisWorkbook.Worksheets("HC_MODULAR_BOARD_20180112")
    Set wsOutput = Workbooks("Output.xlsx").Worksheets("Sheet1")
    
    With wsInput
        LastRow = .Cells(.Rows.Count, "i").End(xlUp).Row
        For i = LastRow To 2 Step -1
            If .Cells(i, "i").Value <> "" Then
                
            Else
                .Rows(i).Delete
            End If
        Next
    End With
    MsgBox "End"
End Sub

To delete rows.

 

[Adrian Low]

The input data is at column "G" and "H" when combining them to the ouput at column "I". I tried your code it can't work, nothing is being copied

 

[DanteAmor]

The code is not copying, it only deletes.
I want to understand which row you want to delete.
You could explain with words exactly what you want to delete.
Forget the macro and the copy a little. we will solve the deletion and then the copying, according?

 

[Adrian Low]

Okay I see what you mean. Kindly refer to this link the https://imgur.com/a/aRNIDMo

Columns "F" and "G" are to be copy and paste in the output at Column "I" but
as you can see from the link there are some blanks over at the columns "F" and "G" I need the blank cells to be deleted and then shift the rest of the values upwards so i can copy over.

 

[Adrian Low]

Another way is to filler for blanks then i delete the entire row lastly then copy the values over but I have no idea how. Could you help?