Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

My search range is column A to G. I want to copy the rows where the values in column G…

  1. 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

  2. Assistant

    To copy the rows where the values in column G are empty, you need to change the If statement 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 myRange to include all columns from A to G and used the CountA function to check if column G is empty instead of comparing the value to an empty string.