1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro for deleting Blank Rows

Discussion in 'VBA Macros' started by suryasabniveesu, Mar 11, 2017.

  1. suryasabniveesu

    suryasabniveesu Member

    Messages:
    30
    I have attached two excel files 1) FormatAAR 2)FormatAAR - New

    1st file is the report (Account Analysis Report) we generate from Oracle ERP and it contains a sheet namely"FNDWRR".

    2nd file is the formatted form of 1st file which we do manually every time we generate from Oracle ERP as per the below are steps:

    1) Create a copy of "FNDWRR" sheet and rename new sheet to "Workings" and Original sheet to "Master". Henceforth all changes will be done in "Workings" sheet only
    2) Delete rows from A1:A25,
    3) Select A2 ("Source") to last row where the report ends. In this case A2:R944. In other files it could be till any row,
    4)Apply filter to above range and then filter "GL Date" and Blanks in G Column,
    5) Delete all blanks and rows which have "GL Date" except the first row of the table,
    6) Delete the columns N, O, P as these columns are not required,
    7) Autofit the data in all columns,
    8) Change the font type to Calibri and size to 11,
    9) Unmerge the A2:A3 rows and delete A3 row,
    10) Finally Save the file.

    All above steps are being done manually whenever we generate an Account Analysis Report from ERP. Earlier I recorded a macro by doing all above steps (except Step 6) and created an Add-in file by assigning a shortcut key. But now that add-in is not serving the purpose as the new report in ERP is having 3 additional columns N,O,P and also the number of last row was fixed to a number. If the data range increases then execution of Add-in doesn't give desired output.

    Request anyone to help me to create an add-in to format the original ERP report like the sample file (i.e FormatARR - New)

    Thanks in Advance
    Surya

    Attached Files:

  2. suryasabniveesu

    suryasabniveesu Member

    Messages:
    30
    Hope everyone in Holi and weekend holidays mood. Could anyone have look about this requirement?
  3. Derek McGill

    Derek McGill Active Member

    Messages:
    129
    This code will delete your columns
    Code (vb):

    Columns("N:P").Delete
     
  4. suryasabniveesu

    suryasabniveesu Member

    Messages:
    30
    I need code for deleting blank rows, formatting the columns and of course deleting N to P columns...
  5. Derek McGill

    Derek McGill Active Member

    Messages:
    129
    And for 7) Autofit the data in all columns, try this code

    Code (vb):

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = False
    For Each Value In Target.Columns
        Worksheets(Sh.Name).Columns(Value.Column).AutoFit
    Next Value
    Application.ScreenUpdating = True
    End Sub
     
  6. Derek McGill

    Derek McGill Active Member

    Messages:
    129
    For 2) Delete rows from A1:A25, Try this code.

    Code (vb):

    Sub sbVBS_To_Delete_Blank_Rows_In_Range()
    Dim iCntr
    Dim rng As Range
    Set rng = Range("A1:A25")
    For iCntr = rng.Row + rng.Rows.Count - 1 To rng.Row Step -1
    If Application.WorksheetFunction.CountA(Rows(iCntr)) = 0 Then Rows(iCntr).EntireRow.Delete
    Next
    End Sub
     
  7. Derek McGill

    Derek McGill Active Member

    Messages:
    129
    suryasabniveesu Have you used any of the code posted or have you solved your problem in any other way ?
  8. suryasabniveesu

    suryasabniveesu Member

    Messages:
    30
    I am not that much familiar with VBA Code hence couldn't spend more time to test the code. I think I have to put all code under one SUB.
  9. Monty

    Monty Well-Known Member

    Messages:
    802
    Try this.

    Simple and faster!

    Code (vb):
    Sub Monty()

    Range("A1:A25").Select
    Selection.SpecialCells(xlCellTypeBlanks).Delete

    End sub

Share This Page