Solved

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

Posted on 2015-01-30
6
66 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 32

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 32

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

822 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