Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

Identify furthest point on spreadsheet with text - to avoid printing multiple blank pages

So if I get a sheet from someone and they have put something in cell F1048576 by accident. I don't know about it and I print the sheet and Excel prints a lot of blank pages...because it thinks I want to print the contents of F1048576. How would I quickly identify the furthest populated cell in the spreadsheet. Pretty sure there is a shortcut for that..but can't remember!
0
agwalsh
Asked:
agwalsh
4 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
In VBA

debug.Print activesheet.usedrange.row, activesheet.usedrange.column
0
 
Rob HensonFinance AnalystCommented:
"Ctrl + End" or "End then Home" will take cursor to last used cell.

Thanks
Rob H
0
 
Danny ChildIT ManagerCommented:
or F5 for Find, and select Print_Area
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
Assuming Print Area has been defined.
0
 
broro183Commented:
hi,

Are you open to a VBA solution?
I posted a "AttemptAtARobustLastCellFinder_v4" function as a solution to another question in my comment ID: 39474286. I have copied & pasted the code into this comment.

There is a lot of code in the AttemptAtARobustLastCellFinder_v4 function, but it can be copied & pasted. No changes need to be made to it. You can run the Sub WhatIsTheLastCell() to get a popup message stating the last used cell for the active sheet. If you want to do something other than get a popup message eg use the result of the function to define the print area, then let me know & I or you can make changes to the sub. On the other hand, if you are happy with the popup box, you can assign a shortcut key combination to the sub via [alt + F8] to bring up the Macro's dialogbox, highlighting the sub and selecting [Options].

Option Explicit

Sub WhatIsTheLastCell()
Dim FeedBackStr As String

    Debug.Print Now

    'change sheet as required
    '   With objLast_Cell
    With AttemptAtARobustLastCellFinder_v4(ActiveSheet)
        FeedBackStr = .Address
        Application.Goto .Cells(1, 1)
    End With

    Debug.Print FeedBackStr
    Debug.Print Now
    MsgBox FeedBackStr, vbOKOnly

End Sub
'***********************************no changes are needed in the rest of the code from here onwards.
Function AttemptAtARobustLastCellFinder_v4(ws As Worksheet) As Range
'inspired by FP's comments about a "binary chop" approach http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380467
'still subject to the limitations of CountA which Qlemo mentioned: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39380520

Dim PercentArr As Variant    'this can probably be written better
Dim PercentageMultiplier As Double
Dim PercentInd As Long    'percent loop index
Dim LastRow As Long
Dim LastCol As Long
Dim RowsInWs As Long
Dim ColsInWs As Long
Dim LoopInd As Long
Dim UpperLim As Long
Dim BlockSizer As Long
Dim FirstRowOfUsedRng As Long

    With ws
        RowsInWs = .Rows.Count
        ColsInWs = .Columns.Count
    End With
    PercentArr = Array(0.5, 0.3, 0.1, 0.05, 0.03, 0.01, 0.005, 0.003, 0.001, 1)

    'run a loop to find the last row
    'v4, amended in case the first row of the used range is not Row 1.
    With ws.UsedRange
        UpperLim = Application.WorksheetFunction.Min(RowsInWs, .Cells(1, 1).Row - 1 + .Rows.Count)
    End With

    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = PercentArr(PercentInd)
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * RowsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                If Application.CountA(ws.Range(LoopInd - BlockSizer + 1 & ":" & LoopInd)) Then
                    Exit For
                End If
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd

    'v4: .max is used to allow for empty sheets
    LastRow = Application.WorksheetFunction.Max(1, UpperLim)

    'run a loop to find the last column
    'v4, amended in case the first column of the used range is not column 1.
    With ws.UsedRange
        UpperLim = Application.WorksheetFunction.Min(ColsInWs, .Cells(1, 1).Column - 1 + .Columns.Count)
    End With
    For PercentInd = LBound(PercentArr) To UBound(PercentArr)

        PercentageMultiplier = PercentArr(PercentInd)
        If PercentageMultiplier <> 1 Then
            BlockSizer = PercentageMultiplier * ColsInWs
        Else
            BlockSizer = 1
        End If

        For LoopInd = UpperLim To 1 Step -BlockSizer
            If (LoopInd - BlockSizer + 1) > 0 Then
                With ws
                    'Searches entire columns
                    'v4 corrected as per http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28203209.html#a39392583
                    If Application.CountA(.Range(.Cells(1, LoopInd - BlockSizer + 1), .Cells(RowsInWs, LoopInd))) Then
                        Exit For
                    End If
                End With
            Else
                Exit For
            End If
        Next LoopInd

        UpperLim = LoopInd
    Next PercentInd

    'v4: .max is used to allow for empty sheets
    LastCol = Application.WorksheetFunction.Max(1, UpperLim)

    '    'User feedback for testing
    Debug.Print "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address
    '    MsgBox "Last row = " & LastRow & vbNewLine & "Last column = " & LastCol & vbNewLine & "Address = '" & ws.Name & "'!" & ws.Cells(LastRow, LastCol).Address

    Set AttemptAtARobustLastCellFinder_v4 = ws.Cells(LastRow, LastCol)

End Function

Open in new window


hth
Rob
0
 
agwalshAuthor Commented:
Thanks to everyone for their help (as always :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now