Hi, I have a workbook with a number of sheets where many rows has no value in column A, but have values in other columns. What the code needs to do is to do is this:
My code is below, can somebody have a look and maybe point me in the right direction of what I have done wrong?
- Loop thru all rows in all sheets
- If a row has no value in column A, copy and append the row to a sheet called "Note", starting in cell A1
My code is below, can somebody have a look and maybe point me in the right direction of what I have done wrong?
VBA Code:
Sub CopyEmptyRowsToNoteSheet()
Dim ws As Worksheet
Dim noteSheet As Worksheet
Dim lastRow As Long
Dim i As Long
Set noteSheet = ThisWorkbook.Sheets("Note")
For Each ws In ThisWorkbook.Sheets
If ws.Name <> "Note" Then
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 1 To lastRow
If ws.Cells(i, "A").Value = "" Then
ws.Cells(i, "A").EntireRow.Copy noteSheet.Cells(noteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")
End If
Next i
End If
Next ws
End Sub