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

 

[Adrian Low]

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

wsInput.Range("F3:F").AutoFilter Field:=6, Criteria1:="="
wsInput.Range("F3:F").SpecialCells(xlCellTypeVisible).Delete
wsInput.ShowAllData
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For Each C In .Range("F3:F" & LastRow)
wsOutput.Cells(C.Row, "I").Value = C & "" & C.Offset(0, 1)
Next C

I've tried this but keep getting this error message of method of range of object _worksheet failed any idea?

 

[DanteAmor]

Try this please

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")
    
    'delete lines
    LastRow = wsInput.Cells(wsInput.Rows.Count, "i").End(xlUp).Row
    For i = LastRow To 2 Step -1
        If wsInput.Cells(i, "F").Value = "" Then
            wsInput.Rows(i).Delete
        End If
    Next
    
    'copy columns F and G
    wsInput.Range("F3:G" & LastRow).Copy wsOutput.Range("I3")
    MsgBox "End"
End Sub

 

[Adrian Low]

Try this please

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")
    
    'delete lines
    LastRow = wsInput.Cells(wsInput.Rows.Count, "i").End(xlUp).Row
    For i = LastRow To 2 Step -1
        If wsInput.Cells(i, "F").Value = "" Then
            wsInput.Rows(i).Delete
        End If
    Next
    
    'copy columns F and G
    wsInput.Range("F3:G" & LastRow).Copy wsOutput.Range("I3")
    MsgBox "End"
End Sub

It works thank you so much. But why does it copy another column again at column "J"?

LastRow = wsInput.Cells(wsInput.Rows.Count, "i").End(xlUp).Row
For i = LastRow To 2 Step -1
If wsInput.Cells(i, "F").Value = "" Then
wsInput.Rows(i).Delete
End If
Next

'copy columns F and G
wsInput.Range("F3:G" & LastRow).Copy wsOutput.Range("I3")
LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For Each C In .Range("F3:F" & LastRow)
wsOutput.Cells(C.Row, "I").Value = C & "" & C.Offset(0, 1)
Next C

I need the offset cause I need to copy both column from column "F" and "G" combing values into "I"

 

[Adrian Low]

I have solve the issue thank you so much DanteAmor.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.