Is this too complicated for VBA?

[thefastlife]

I'm trying to automate monthly reports I run.

Here's what I currently do:
1. Grab data from one file
2. Overwrite said data into a "data" tab in a workbook with multiple tabs
3. Each tab is run off a pivot table. Once the new data is in, I refresh and all the tabs' pivots are updated.
4. I export each tab as its own workbook.
5. I hide all rows and columns that do not contain data and merge and center the top column.

These reports get distributed among a bunch of teams, hence the formatting.

I think everything can be done relatively easily except the hiding of rows and columns as that's not necessarily constant.

Any thoughts?

 

[My Aswer Is This]

In Excel 2013 we have
1.5 million rows and
16,384 columns

I do not believe it's possible to hide all of these except the ones with data.

 

[thefastlife]

In Excel 2013 we have
1.5 million rows and
16,384 columns

I do not believe it's possible to hide all of these except the ones with data.

What I do now, in my manual process, is highlight the first empty column and hit ctrl+shift+right arrow to highlight everything to the right and then hide.

Likewise with the rows.

So, not sure?

 

[thefastlife]

Maybe I'm wasting my time trying? :/

 

[My Aswer Is This]

If you want all these process automated you could use the macro recorder
or if you wanted a script written you would have to give a lot more details.

 

[MARK858]

What I do now, in my manual process, is highlight the first empty column and hit ctrl+shift+right arrow to highlight everything to the right and then hide.

Likewise with the rows.

So, not sure?

If you are literally saying hide all the columns after the last used column and rows after the last used row try the code below.

Sub HideIt()

    Dim LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

        With ActiveSheet
            LastRow = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(1, Columns.Count)).EntireColumn.Hidden = True
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, 1)).EntireRow.Hidden = True
        End With
  On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

 

[thefastlife]

If you are literally saying hide all the columns after the last used column and rows after the last used row try the code below.

Sub HideIt()

    Dim LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

        With ActiveSheet
            LastRow = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(1, Columns.Count)).EntireColumn.Hidden = True
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, 1)).EntireRow.Hidden = True
        End With
  On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Almost but for some reason that hid everything, including my data

Maybe I typed the code wrong?

 

[S.H.A.D.O.]

Copy and paste MARK858's code into a Module to avoid errors.

 

[MARK858]

Maybe I typed the code wrong?

Select the code and copy and paste it not type it.


Edit: slow typing caused an echo of the previous post

 

[thefastlife]

If you are literally saying hide all the columns after the last used column and rows after the last used row try the code below.

Sub HideIt()

    Dim LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

        With ActiveSheet
            LastRow = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(1, Columns.Count)).EntireColumn.Hidden = True
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, 1)).EntireRow.Hidden = True
        End With
  On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

Copy and paste MARK858's code into a Module to avoid errors.

Select the code and copy and paste it not type it.


Edit: slow typing caused an echo of the previous post

That is too awesome. I'm a noob to VBA so learning to automate is sort of mind blowing.