Solved

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

Posted on 2015-01-30
6
69 Views
Last Modified: 2016-02-11
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
Comment
Question by:agwalsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 125 total points
ID: 40579327
In VBA

debug.Print activesheet.usedrange.row, activesheet.usedrange.column
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 40579332
"Ctrl + End" or "End then Home" will take cursor to last used cell.

Thanks
Rob H
0
 
LVL 23

Assisted Solution

by:Danny Child
Danny Child earned 125 total points
ID: 40580402
or F5 for Find, and select Print_Area
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40581668
Assuming Print Area has been defined.
0
 
LVL 10

Expert Comment

by:broro183
ID: 40585666
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
 

Author Closing Comment

by:agwalsh
ID: 40602566
Thanks to everyone for their help (as always :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question