A macro to format data

[tomberlins]

I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.

Is there a way to delete the last couple of lines of data of a spreadsheet?

[Palmetto]

Sub Delete_Last_2rows()

    Dim lrow As Long
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row - 1
    
    Range("A" & lrow, Range("A" & lrow + 1)).EntireRow.Delete

End Sub

Counts rows down column-A and deletes last two rows.

[tomberlins]

So I have never typed the language to produce a macro I just simply record the steps I use to format, can I just copy and paste what you entered into my current macro to make it work?

Follow up to the overall issue, I need to delete lines that have "0" or "-" in particular columns can I write language to do that too?

[Palmetto]

can I just copy and paste what you entered into my current macro to make it work?

As it is just basic, somewhat "generic" code you ** should not ** have any problems with just appending it to your current code (probably at the end of your current code).

Follow up to the overall issue, I need to delete lines that have "0" or "-" in particular columns can I write language to do that too?

Yes. VBA can be written for this, but you should supply more details or post a sample workbook.

[tomberlins]

I have added a sample booklet. Of course I tried to remove any sensitive data. Basically I need the first two rows deleted, the third row replaced with a header row, the last two rows deleted, and than any row that contains a "0" or "-" in the two bolded columns to be deleted.

[Palmetto]

Option Explicit
Sub Format_Sheet()

    Dim lrow As Long, lcol As Long, c As Range

    Application.ScreenUpdating = False

    Rows("1:2").Delete
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row - 1
    
    Range("A" & lrow, Range("A" & lrow + 1)).EntireRow.Delete
    
    On Error Resume Next
    For Each c In Range("F1:G" & lrow)
        If c.Value = "-" Or c.Value <= 0 Then
            c.EntireRow.Delete
        End If
    Next c
    
    lcol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Columns(lcol).EntireColumn.Delete
    
    Range(Cells(1, 1), Cells(1, lcol)).Columns.AutoFit

    Application.ScreenUpdating = True

End Sub

EDIT: overlook the comment to delete a column. Code is now amended for this requirement, as well as including code to autofit the columns per the contents of cells in row 1. Attached new copy of the workbook with revised code.