VBA Question

[JoeyGaspard]

Hi all, I am trying to get the below code to work, but I have something wrong. In the sections in Bold, I need it to filter the data, and delete all the entire rows containing the filtered word in "Column C" Please help! Thanks in advance!

Sub Update()


Application.ScreenUpdating = False

Sheets("Import").Select
Range(Range("A:F"), Range("A:F").End(xlDown)).Copy
Worksheets("Inventory").Range("A:F").PasteSpecial Paste:=xlPasteValues




With Sheets("Inventory")
.AutoFilterMode = False
.Range("C1").AutoFilter 3, "USCOLDSTO"
.AutoFilter.Range.Columns("A:F").Offset(1).Copy Sheets("USCOLD").Range("A2")
.AutoFilterMode = False
End With

With Sheets("Inventory")
.AutoFilterMode = False
.Range("C1").AutoFilter 3, "USCOLDSTO"
.AutoFilter.Range.Columns("A:F").Offset(1).Delete
.AutoFilterMode = False
End With

With Sheets("Inventory")
.AutoFilterMode = False
.Range("C1").AutoFilter 3, "Fzr Dock"
.AutoFilter.Range.Columns("A:F").Offset(1).Delete
.AutoFilterMode = False
End With





ThisWorkbook.RefreshAll

Sheets("Inventory").Select

Application.ScreenUpdating = True

End Sub

 

[offthelip]

A much better and faster way of doing what you are trying to do is rather than copying all the data and then trying to delete rows ( which is very slow if there are lots of them) is to do all the processing in memory using variant arrays . This code copies all the data from "Import" into memory then runs through it in memory and only copies rows that don't have USCOLDSTO in column Cto a second variant array and then writes the array out toe the inventory sheet.

Sub test()
Dim outarr()
With Sheets("Import")
lastcol = 6 ' Column F
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
datar = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))  ' pick up all the data from Import Sheet
End With
ReDim outarr(1 To lastrow, 1 To lastcol)
  ' now copy the rows that don't have USCOLD in column 3
indi = 1 ' set first output row
   For i = 1 To lastrow
    If datar(i, 3) <> "USCOLDSTO" Then
     For j = 1 To lastcol
      outarr(indi, j) = datar(i, j)
     Next j
     indi = indi + 1
    End If
   Next i
 With Sheets("Inventory")
 
 .Range(.Cells(1, 1), .Cells(indi - 1, lastcol)) = outarr
 End With

End Sub

You can add more conditions to the If statement to include other words by using the AND construct

 

[JoeyGaspard]

A much better and faster way of doing what you are trying to do is rather than copying all the data and then trying to delete rows ( which is very slow if there are lots of them) is to do all the processing in memory using variant arrays . This code copies all the data from "Import" into memory then runs through it in memory and only copies rows that don't have USCOLDSTO in column Cto a second variant array and then writes the array out toe the inventory sheet.

Sub test()
Dim outarr()
With Sheets("Import")
lastcol = 6 ' Column F
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
datar = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))  ' pick up all the data from Import Sheet
End With
ReDim outarr(1 To lastrow, 1 To lastcol)
  ' now copy the rows that don't have USCOLD in column 3
indi = 1 ' set first output row
   For i = 1 To lastrow
    If datar(i, 3) <> "USCOLDSTO" Then
     For j = 1 To lastcol
      outarr(indi, j) = datar(i, j)
     Next j
     indi = indi + 1
    End If
   Next i
 With Sheets("Inventory")
 
 .Range(.Cells(1, 1), .Cells(indi - 1, lastcol)) = outarr
 End With

End Sub

You can add more conditions to the If statement to include other words by using the AND construct

Thank you so much! I would like to also exclude the lines that have Fzr Dock in Column c, Im not sure I understand what all to use after the AND statement to make that happen? I tried but I am missing something?

 

[JoeyGaspard]

A much better and faster way of doing what you are trying to do is rather than copying all the data and then trying to delete rows ( which is very slow if there are lots of them) is to do all the processing in memory using variant arrays . This code copies all the data from "Import" into memory then runs through it in memory and only copies rows that don't have USCOLDSTO in column Cto a second variant array and then writes the array out toe the inventory sheet.

Sub test()
Dim outarr()
With Sheets("Import")
lastcol = 6 ' Column F
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
datar = .Range(.Cells(1, 1), .Cells(lastrow, lastcol))  ' pick up all the data from Import Sheet
End With
ReDim outarr(1 To lastrow, 1 To lastcol)
  ' now copy the rows that don't have USCOLD in column 3
indi = 1 ' set first output row
   For i = 1 To lastrow
    If datar(i, 3) <> "USCOLDSTO" Then
     For j = 1 To lastcol
      outarr(indi, j) = datar(i, j)
     Next j
     indi = indi + 1
    End If
   Next i
 With Sheets("Inventory")
 
 .Range(.Cells(1, 1), .Cells(indi - 1, lastcol)) = outarr
 End With

End Sub

You can add more conditions to the If statement to include other words by using the AND construct

Thank you so very much! I figured out the AND construct and it is working beautifully!! I really do appreciate your help!

 

[offthelip]

I figured out the AND construct and it is working beautifully!!

Glad to have helped. It is always better to get to the final solution under your own steam then you really understand what is happening.