Deleoing unused rows &columns(formatting ,color etc ) therein for all worksheet in wkbook

[amlan009]

Dear Experts ,


Firstly i must admit that this forum is making me feel so much happy and i must admit that i have had the good fortune of experts taking precious time to help me ,so i take a moment to thank all of them before i proceed to explain my new project !

Like the title suggest's ,i have a output file in which i see that the formatting gets applied even across unused range as well -Both rows and columns -please see attachment !

If you closely see the attached workbook ,in the tabs(worksheets of this workbook) ,the used rows in each worksheet are not always the same ,

For example in some tabs (worksheets ), i have 3 rows ,in some i have 7 rows ......

Similarly in each of the tabs (worksheets ) i have 5 columns filled ,in some i have 6 ......

Is there a macro which can go through each of the wks and delete all the blank rows and columns and the formatting (color's ,boundaries etc )therein !

Any help will be much appreciated ,

Humble Regards ,


Amlan Dutta

[amlan009]

Will appreciat help!!!!

[protonLeah]

Use the macro recorder.

[amlan009]

Dear ,

Thanks for your time to have responded .In this case macro recorder can't be a solution .Since the different tabs(worksheets) of the workbook will have different used ranges for columns and rows in each of the worksheets(tabs) ,so This approach wont work ,


Let me explain -say ,i do a macro recording on first tab(worksheet) which has say 5 rows and 7 columns (used range ) ,Thm macro recorder will calculate used range based on tab 1 then i can't expect it to work on my other worksheets (tabs ) which has x used rows and y used columns(else even used range will get deleted ) .The approach would work if all the tabs have same X no of rows and y no of columns .



In my actual case i have 325 sheets with different used rows i.e ,x1,x2,x3,x4,etc no of rows in each of the sheets and y1,y2,y3,y4,y5 etc no of used columns in each of the sheets .

I think that you shall now agree that macro recording in my case wont solve it ,since used ranges of rows and columns differ in each of the tabs (worksheets )of my workbook .

On the bright side ,I tried my hands at a code which i got after a bit of research (i found many codes after reserach but i liked only this one since the coding is simple to undrstand and logical ,but it was for deleting empty rows and not columns( which i havent managed ) ,i type the code below - i hope someone can modify it to suit my needs,

Sub DeleteBlankRowsandcolumns()
     
    Dim WS As Worksheet, R As Long, X As Long, Rngr As Range, Rngc As Range
    On Error GoTo End
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
     
    For Each WS In Worksheets
        Set Rngr = WS.UsedRange.Rows
        Set Rngc = WS.UsedRange.Columns
        For R = Rngr.Rows.Count To 1 Step -1
            C = Rngc.Columns.Count To 1 Step 1
            If Application.WorksheetFunction.CountA(Rngr.Rows(R).EntireRow) = 0 Then
                Rngr.Rows(R).EntireRow.Delete
                
               If Application.WorksheetFunction.CountA(Rngc.Columns(C).EntireColumn) = 0 Then
                Rngc.Column(C).EntireColumn.Delete
                
            End If
            
            End If
            
        Next R
    Next WS
     
End:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
     
End Sub

I know that i am using the column part in the loop in the wrong fashion ,if someone can edit the code to make it workable ,i will be really glad !

Thanks again to everyone who would be looking into this ,i really really appreciate !



Regards ,


Amlan Dutta