Solved

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

Posted on 2015-01-30
6
64 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
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 31

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:DanCh99
DanCh99 earned 125 total points
ID: 40580402
or F5 for Find, and select Print_Area
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 31

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now