My search range is column A to G. I want to copy the rows where the values in column G…
- User
My search range is column A to G. I want to copy the rows where the values in column G are empty. The code below is not doing this correctly.
Sub CopyEmptyPOWorkbook() 'Application.EnableEvents = False
Dim i As Long, lastRow As Long Dim myRange As Range, myCell As Range Dim text As String
' Define the range to search for empty cells in column G lastRow = Cells(Rows.count, "A").End(xlUp).Row Set myRange = Range("A1:A" & lastRow)
' Create a new workbook Dim newWb As Workbook Set newWb = Workbooks.Add
' Add a new sheet and name it Dim newWs As Worksheet Set newWs = newWb.Sheets.Add(After:=newWb.Sheets(newWb.Sheets.count)) newWs.Name = "Results"
' Add the headers to row 1 newWs.Cells(1, 1).Value = "COMPANY" newWs.Cells(1, 2).Value = "WORK" newWs.Cells(1, 3).Value = "QUOTE REF" newWs.Cells(1, 4).Value = "VALUE" newWs.Cells(1, 5).Value = "P REQUEST" newWs.Cells(1, 6).Value = "PR DATE" newWs.Cells(1, 7).Value = "P ORDER"
' Loop through each cell in the range and paste the corresponding non-blank rows into individual cells in a new row Dim newRow As Long newRow = 1 For Each myCell In myRange If myCell.Value <> "" Then newRow = newRow + 1 ' Move to the next row
For i = 1 To 7 ' Assuming data range is from A to G If Cells(myCell.Row, i + 6).Value = "" Then ' Check for empty cells in Column G newWs.Cells(newRow, i).Value = Trim(Cells(myCell.Row, i).Value)
End If Next i End If Next myCell
' Open the new workbook for the user to examine newWb.Activate
' Show a message box with instructions to the user MsgBox "Please examine the new workbook and make any necessary changes before saving it. To save the file, click File > Save As, or use the keyboard shortcut Ctrl+S.", vbInformation, "New Workbook Created"
'Application.EnableEvents = True End Sub
- Assistant
To copy the rows where the values in column G are empty, you need to change the
Ifstatement in the loop to check for empty cells in column G instead of checking if the value in the current cell is not blank. Here's the updated code:Sub CopyEmptyPOWorkbook() 'Application.EnableEvents = False
Dim i As Long, lastRow As Long Dim myRange As Range, myCell As Range Dim text As String
' Define the range to search for empty cells in column G lastRow = Cells(Rows.count, "A").End(xlUp).Row Set myRange = Range("A1:G" & lastRow)
' Create a new workbook Dim newWb As Workbook Set newWb = Workbooks.Add
' Add a new sheet and name it Dim newWs As Worksheet Set newWs = newWb.Sheets.Add(After:=newWb.Sheets(newWb.Sheets.count)) newWs.Name = "Results"
' Add the headers to row 1 newWs.Cells(1, 1).Value = "COMPANY" newWs.Cells(1, 2).Value = "WORK" newWs.Cells(1, 3).Value = "QUOTE REF" newWs.Cells(1, 4).Value = "VALUE" newWs.Cells(1, 5).Value = "P REQUEST" newWs.Cells(1, 6).Value = "PR DATE" newWs.Cells(1, 7).Value = "P ORDER"
' Loop through each cell in the range and paste the corresponding non-blank rows into individual cells in a new row Dim newRow As Long newRow = 1 For Each myCell In myRange.Rows If Application.WorksheetFunction.CountA(myCell.Offset(0, 6)) = 0 Then ' Check for empty cells in Column G newRow = newRow + 1 ' Move to the next row For i = 1 To 7 ' Assuming data range is from A to G newWs.Cells(newRow, i).Value = Trim(myCell.Cells(1, i).Value) Next i End If Next myCell
' Open the new workbook for the user to examine newWb.Activate
' Show a message box with instructions to the user MsgBox "Please examine the new workbook and make any necessary changes before saving it. To save the file, click File > Save As, or use the keyboard shortcut Ctrl+S.", vbInformation, "New Workbook Created"
'Application.EnableEvents = True End Sub
Note that I also changed the range for
myRangeto include all columns from A to G and used theCountAfunction to check if column G is empty instead of comparing the value to an empty string.