Need a macro to copy rows only when certain columns are completely empty

profklein

New Member
Joined
May 20, 2017
Messages
20
Hello all
I am cleaning my data in stages. For my first stage, I want to eliminate all the rows where my survey participant did not answer any questions (meaning he or she wasn't truly a participant). I will know this if columns F through BK are completely empty. So what is need is this: Starting with row 3 in sheet x, I want to copy that row into sheet y as long as columns F through BK are not COMPLETELY EMPTY. I want to do this for every row until the last.

Thank you in advance for helping me with my PhD.

Gene
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,853
Office Version
  1. 2019
Platform
  1. Windows
Here is an untested VBA solution

Code:
Option Explicit
Sub Foo()
    Dim x As Worksheet
    Dim y As Worksheet
    Set x = Sheets("SheetX")
    Set y = Sheets("SheetY")
    Dim i As Long, lr As Long
    Dim lry As Long
    Application.ScreenUpdating = False
    lr = x.Range("B" & Rows.Count).End(xlUp).Row
    For i = 3 To lr
        lr2 = y.Range("A" & Rows.Count).End(xlUp).Row + 1
        If Application.WorksheetFunction.Count(Range("F" & i & ":BK" & i)) > 0 Then
            x.Range("B" & i).EntireRow.Copy
            y.Range("A" & lry).PasteSpecial xlPasteValues
        End If
    Next i
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    MsgBox "Completed"
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,028
Messages
5,526,335
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top