0

In the following code the cell value of "m3" and "% Pieces" is not recognized, neither are all the cells which are empty recognized. If I repeat those 3 times than it is applied to all the cells which meet the criteria. How do I write the code in such way so I don't have to repeat it 3 times? I have checked the spelling multiple times.


Dim Summary As Workbook
Dim Osheet As Worksheet
Dim Dsheet1 As Worksheet
Dim Dsheet2 As Worksheet
Dim cel As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range

TurnOffFunctionality
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set Summary = Workbooks("Quesnel Sawmill Monthly Production Summary - Blank.xlsm")
Set Osheet = ActiveWorkbook.Worksheets("Sheet1")
Set Dsheet1 = Summary.Worksheets("Sorter 1 Production")
Set Dsheet2 = Summary.Worksheets("Sorter 2 Production")
Set Rng1 = Osheet.Range("C:U")
Set Rng2 = Osheet.Range("E12:E100")
Set Rng3 = Osheet.Range("A12:V12")

With Osheet
        Rng1.Unmerge
        Rng1.WrapText = False
        
        Osheet.Range("E12:E100").TextToColumns Destination:=Range("E12"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
        
        For Each cel In Rng2
        If cel.Value = "FBM" Then
        cel.EntireRow.Delete
        ElseIf cel.Value = "m3" Then
        cel.EntireRow.Delete
        ElseIf cel.Value = "% FBM" Then
        cel.EntireRow.Delete
        ElseIf cel.Value = "% Pieces" Then
        cel.EntireRow.Delete
        ElseIf cel.Value = "" Then
        cel.EntireRow.Delete
        End If
        Next
        
        For Each cel In Rng3
        If cel.Value = "" Then
        cel.EntireColumn.Delete
        End If
        Next
        
        Range("A18").ClearContents
        Columns("B:J").AutoFit
End With

TurnOnFunctionality
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
Jesse Koene
  • 1

1 Answers1

-1

Whenever you loop and delete rows things get messy. Delete is very time consuming. I would recommend put your row data in a temp sheet or array clean your master sheet do the calculations and put back the clean data.

Rafael Carvalho
  • 30
  • 5