• 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...

  • 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

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
 

Attachments

  • FormatAAR - New.xlsm
    94.8 KB · Views: 2
  • FormatAAR.xlsm
    71.7 KB · Views: 1
And for 7) Autofit the data in all columns, try this code

Code:
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
 
For 2) Delete rows from A1:A25, Try this code.

Code:
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
 
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.
 
Back
Top