CODEWITHSUNDEEP
excelexcel-2007vba
SteveW1968
SteveW1968

Reputation: 23

Copy entire rows based on multiple cell values

I am trying to copy entire rows where two values are blank and the third cell value is MS-NORT,
My code is below but I am getting a syntax error.

please help

Set MK = Sheets("data dump").Range("P1:Q5000")
For Each cell In MK

If cell.Value = "" And Sheets("Data Dump").Range("M1:M5000") <> "MS-NORT" 
Then cell.EntireRow.copy
Sheets("working data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
End If
Application.CutCopyMode = False
Next

Upvotes: 0

Views: 1366

Answers (1)

user688334
user688334

Reputation:

I'm not exactly sure what you mean by "where two values are blank and the third cell value is MS-NORT"

I've assumed you want to check each row and if column M <> "MS-NORT" and both columns P and Q are blank then copy the row. This code will do that

Sub test()
Dim rngRow As Range, MK As Range, lngrow As Long
Dim varCheckCol1 As Variant, varCheckCol2 As Variant, varCheckCol3 As Variant

Set MK = Sheets("data dump").Range("M1:Q5000")
For Each rngRow In MK.Rows

  varCheckCol1 = rngRow.Value2(1, 1) 'col M
  varCheckCol2 = rngRow.Value2(1, 4) 'col P
  varCheckCol3 = rngRow.Value2(1, 5) 'col Q

  If IsEmpty(varCheckCol2) And IsEmpty(varCheckCol3) And varCheckCol1 <> "MS-NORT" Then
     rngRow.EntireRow.Copy
     Sheets("working data").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
  End If

Next

End Sub

Upvotes: 1

Related Questions