Search code examples
csvvbscriptrow

vbscript code to delete a row in a csv file where cell is empty

I have a csv file that I need to have converted to an xlsx file (not a problem)...in the process of doing that I format my data. I want to delete all rows where I have an empty cell. Column C had text data and Column D has numeric data.

I have tried : If (.Sheets(1).Columns.Cells("D")) = "" Then msgbox("HI") End If

I've tried .text as well as .value I can't seem to get into the actual cells to determine if it's empty so as to delete the row.

Solution

  • You need a little more structure to achieve your goal. You can't check every cell in Column D just by using .Cells("D") = "" as the Cells expects both row and column indicators to be provided.

    Perhaps you could try looping over your data in Column C and/or D and where there's an empty cell, delete the whole row. 

    Const xlUp = -4162    ' Excel variables are not defined in vbscript
Dim oBook : Set oBook = ThisWorkbook
Dim oSheet : Set oSheet = oBook.Sheets(1)
Dim iLastRow, iRow
iLastRow = oSheet.Cells(oSheet.Rows.Count, 3).End(xlUp).Row
For iRow = iLastRow to 2 Step -1 'assumes a header row otherwise use 1 instead of 2
    If oSheet.Range("C" & iRow) = "" Then
        oSheet.Range("C" & iRow).EntireRow.Delete ' delete row if blank
    End If
Next

    Edit after comment: Changed the loop to run from iLastRow to 2 instead of 2 to iLastRow. When deleting rows, you should always move from the bottom up, as when you delete a row, the next one jumps up and is then skipped over!