Cut and paste row if columns AC-AF contain blanks

Question

What I am trying to accomplish is this:
If any cells in columns AC-AF in my entire worksheet are blank, cut the entire row and paste to a new worksheet labeled "MissingShipping".
Code should adjust with the amount of rows, since that will never be the same.

From examples I have seen I don't understand where to insert the range of the cells I want to wade through.

I get the error

"Method 'Range' of object'_Worksheet'

on the line NewSetup.Range(Cells(Destinationrow, 1), Cells(Destinationrow, lastcolumn)).Select.

Option Explicit
Sub Shipping()
    Dim MissingShipping As Worksheet
    Set MissingShipping = Sheets.Add(After:=Sheets(Sheets.Count))
    MissingShipping.Name = "MissingShipping"
    Dim NewSetup As Worksheet
    Dim lastcolumn As Integer
    Dim Destinationrow As Integer
    Dim lastrow As Long
    Set NewSetup = Worksheets("NKItemBuildInfoResults")
    Set MissingShipping = Worksheets("MissingShipping")
    Destinationrow = 1
    lastcolumn = NewSetup.Range("XFD1").End(xlToLeft).Column
    lastrow = NewSetup.Range("A1048576").End(xlUp).Row
    Dim i As Long
    Dim j As Long
    For i = lastrow To 1 Step -1
        For j = 1 To lastcolumn
            If NewSetup.Cells(i, j).Value = "" Then
                NewSetup.Activate
                NewSetup.Range(Cells(i, 1), Cells(i, lastcolumn)).Cut
                MissingShipping.Activate
                NewSetup.Range(Cells(Destinationrow, 1), Cells(Destinationrow, _
                  lastcolumn)).Select
                ActiveSheet.Paste
                NewSetup.Rows(i).Delete shift:=xlUp
                Destinationrow = Destinationrow + 1
                Exit For
            End If
        Next j
    Next i
End Sub

Answer 1

G'day Nikki,

Welcome to the world of VBA! There are plenty of great resources on the internet to help you on your journey.

It's often easier and faster to work with a range inside your code instead of reading and writing to a sheet and selecting cells to mimic things that you would normally do if you were doing the job manually.

It's a good idea to get your head around the range object early on. It's handy for working with multiple worksheets.

The following is a good start with Ranges in Excel:

https://excelmacromastery.com/excel-vba-range-cells/

Another handy thing is a collection. If you had to store a bunch of things to work with later on, you can add them to a collection then iterate over them using a "For Each" loop. This is a good explanation of collections:

https://excelmacromastery.com/excel-vba-collections/

I had a quick look at your code and using the concept of Ranges and Collections, I have altered it to do what I think you were trying to do. I had to make a few assumptions as I haven't seen you sheet. I ran the code on a bunch of random rows on my computer to make sure it works. Consider the following:

Dim MissingShipping As Worksheet
Dim NewSetup As Worksheet

Dim rangeToCheck As Range
Dim cellsToCheck As Range
Dim targetRange As Range
Dim rw As Range 'rw is a row
Dim cl As Range 'cl is a cell

Dim rowColl As New Collection

Dim i As Long

Set NewSetup = Worksheets("NKItemBuildInfoResults")
Set MissingShipping = Worksheets("MissingShipping")

'Get the range of data to check
Set rangeToCheck = NewSetup.Range("A1").CurrentRegion

'For each row in the range
For Each rw In rangeToCheck.Rows

    'For the last four cells in that row
    Set cellsToCheck = rw.Cells(1, 29).Resize(1, 4)

    For Each cl In cellsToCheck.Cells

        'If the cell is empty
        If cl.Value = "" Then

            'Add the row to our collection of rows
            rowColl.Add rw

            'Exit the for loop because we only want to add the row once.
            'There may be multiple empty cells.
            Exit For

        End If

    'Check the next cell
    Next cl

Next rw

'Now we have a collection of rows that meet the requirements that you were after

'Using the size collection of rows we made, we now know the size of the range 
'we need to store the values
'We can set the size of the new range using rowColl.Count 
'(that's the number of rows we have)
Set targetRange = MissingShipping.Range("A1").Resize(rowColl.Count, 32)

'Use i to step through the rows of our new range
i = 1

'For each row in our collection of rows
For Each rw In rowColl

    'Use i to set the correct row in our target range an make it's value 
    'equal to the row we're looking at
    targetRange.Rows(i) = rw.Value

    'Increment i for next time
    i = i + 1

Next rw

End Sub

Good luck! Hope this helps.