mardi 3 juillet 2018

VBA If columns AC-AF contain blanks

I just started teaching myself VBA less than a week ago, so I am very inexperienced and could really use some help! I've been trying to automate some processes, and I've been able to figure out quite a bit. However some things do not seem to be working for me, despite the research that I have done. If anyone would mind helping me out and explaining where I'm going wrong. I'm sure my issue is with lack of understanding the coding.

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

From the other examples I have seen I don't understand where to insert the range of the cells I want it to wade through? I also keep getting the error "Method 'Range' of object'_Worksheet' on the line: "NewSetup.Range(Cells(Destinationrow, 1), Cells(Destinationrow, lastcolumn)).Select". If someone could please help me understand I would very much appreciate it!

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

Aucun commentaire:

Enregistrer un commentaire