0

I have a spreadsheet with sales data for a given month for a number of different stores. There are approximately 450 rows (stores) to go through. The days of the month are in row 1, starting in column B, with the store numbers in column A, starting in row 2. What I need to find are the rows that are missing data for different days. I can use conditional formatting to highlight the cells that are empty, but that is still a lot to look at. What I am trying to do is reduce the amount of cells to look at by deleting any rows that are not missing data. So if a row is not missing any days of data, then I don't need to look at it, and can delete it. But if it is missing data, then keep the row. I'm stuck on figuring out the actual code, but I have come up with pseudo code for what I want:

foreach row in range ($B$2:$AB$450)
    foreach cell in row 
        if cell < 1
            delete row
    end foreach
end foreach

Here is an example of the spreadsheet:

        A           B               C               D
1               12/1/2019       12/2/2019       12/3/2019
2   Site 1          1000                            2000
3   Site 2          5000            5000            3000
4   Site 3          6000            4000            3000

In this example, rows 4 and 4 would be deleted because they are not missing any data.

Thanks

Harlan
  • 133
  • 1
  • 3
  • 15
  • 1
    Does this answer your question? [Delete a row in Excel VBA](https://stackoverflow.com/questions/7851859/delete-a-row-in-excel-vba) – braX Dec 30 '19 at 15:03
  • https://stackoverflow.com/questions/29006416/excel-vba-if-cell-value-then – braX Dec 30 '19 at 15:04
  • 1
    Simple solution **1.** After the last column, add this formula `=COUNTA(A1:D1)` and pull it down. **2.** Autofilter the data on anything `= 4` and delete it. You may want to see how to use Autofilter to delete the data based [on a condition](https://stackoverflow.com/questions/11317172/delete-row-based-on-condition) – Siddharth Rout Dec 30 '19 at 15:10
  • 1
    @SiddharthRout This seems to be the quickest and easiest solution. Thanks. – Harlan Dec 30 '19 at 15:57
  • I've used this code without any problems. `ThisWorkbook.Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete` change the worksheet name as needed. – GMalc Dec 30 '19 at 16:36

1 Answers1

0

This throws an error if there are no blank cells, so we just resume next in that case. Doesn't delete the row (because that will cause the data to shift up), just hides the whole row.

Dim salesRng as Range
Dim colCount as Long
Set salesRng = Range("B2:AB450")

For Each row in salesRng
    On Error Resume Next
    If row.SpecialCells(xlCellTypeBlanks).count = 0 Then
            row.hidden = true
        End If
Next row
jclasley
  • 668
  • 5
  • 14