Help Clean up code

[zack_]

Hi Everyone,

The purpose of this macro is to import a text file from Oracle that is pretty ugly, eliminate characters, and make it usable in excel. The macro works fine. I tried to make it more robust by allowing the user to choose the text file they want (different sites will select the file from different saved network paths).

I was hoping someone might spend a few minutes to check out the code and suggest more efficient ways to do something or just point out any bad coding. I would be grateful. I've read a few books and am to the point where I can get things accomplished. But I am very far from writing anything elegant and simple.

I appreciate any suggestions in advance

Sub MFGVarianceFormat()
' Purpose: Import and format material variance report from oracle.


' Clear old report
Worksheets("Gentilly MFG Variance").Cells(11, 1).Resize _
    (Rows.Count - 10, Columns.Count).Delete


'Set file name and path to variable to be used in text import wizard
Dim FileName As String
FileName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", _
    Title:="Select TEXT file ONLY", MultiSelect:="False")


'If no text file selected end macro. If text file selected, import file
If FileName = "False" Or FileName = " " Then
    Exit Sub
Else
    
'Import text wizard


With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & FileName & "", Destination:=Range("$A$11"))
        .Name = "Material_Variance-012315"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(12, 39, 4, 15, 17, 16, 18, 16, 16, 15)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End If


'Find last row with data
Dim i As Integer
Dim iFinalRow As Integer
iFinalRow = Cells(Rows.Count, 1).End(xlUp).Row


'Find the starting cell for loop
Cells.Find(What:="description", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


'Loop through several conditions to eliminate blank rows, headers, and characters


For i = iFinalRow To ActiveCell.Row + 1 Step -1
If IsEmpty(Cells(i, 2)) Then
    Cells(i, 1).EntireRow.Delete
ElseIf (IsNumeric(Cells(i, 6))) - (IsEmpty(Cells(i, 6))) = 1 Then
    Cells(i, 3).EntireRow.Delete
ElseIf Cells(i, 5).Value = "" Then
    Cells(i, 2).EntireRow.Delete
ElseIf Not IsNumeric(Cells(i, 5)) Then
    Cells(i, 5).EntireRow.Delete
Else
    ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
End If
Next i


'Turn on filter
ActiveSheet.Range("A21:K21").AutoFilter
    
'Format and move run date and time into rows 11-13
Rows("15:19").EntireRow.Delete
Worksheets("Gentilly MFG Variance").Range("A12:I15").ClearContents
Worksheets("Gentilly MFG Variance").Range("J15:k15").ClearContents
Range("J12:K14").Cut Destination:=Range("A11")
Application.CutCopyMode = False


With Range("A11:B13")
    .HorizontalAlignment = xlLeft
    .Font.Bold = True
    .Interior.ColorIndex = 6
End With


'Label and size columns
Cells(16, 3).Value = "UOM"
Cells(16, 4).Value = "STD Cost"
Cells(16, 5).Value = "STD Units"
Cells(16, 6).Value = "STD Dollars"
Cells(16, 7).Value = "Actual Units"
Cells(16, 8).Value = "Actual Dollars"
Cells(16, 9).Value = "Usage Variance Units"
Cells(16, 10).Value = "Usage Variance Dollars"
Cells(16, 11).Value = "Usage Percentage %"


Columns("F:K").AutoFit


'Filter and highlight Total rows
ActiveSheet.Range("A16:K16").AutoFilter _
    field:=2, Criteria1:="Total" & "*"
Range("A16").CurrentRegion.Offset(1).Interior.ColorIndex = 15
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("A16:K16").AutoFilter


'Clear format last row
Dim afterMacroFinalRow As Integer
afterMacroFinalRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A" & afterMacroFinalRow + 1).EntireRow.ClearFormats


Cells(16, 1).Select


End Sub

 

[Michael M]

Don't have Excel at the moment, but a couple of things

For i = iFinalRow To ActiveCell.Row + 1 Step -1
    If IsEmpty(Cells(i, 2)) Or Cells(i, 5).Value = "" Or Not IsNumeric(Cells(i, 5)) _
        Or (IsNumeric(Cells(i, 6))) - (IsEmpty(Cells(i, 6))) = 1 Then
            Rows(i).Delete
        Else
            ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
    End If
Next i

Worksheets("Gentilly MFG Variance").Range("A12:I15,J15:k15").ClearContents

 

[zack_]

Hey Michael M - thanks for the reply! Both of those suggestions make a lot of sense. I especially like "Rows(i).Delete" -- that never dawned on me for whatever reason.