CODEWITHSUNDEEP
vbaexcel
user2755920
user2755920

Reputation: 13

VBA - Problems dynamically selecting a range

I am new to VBA and am trying to write a macro that takes a workbook with several sheets of data and formats it for printing. Each sheet has tables of information (like operating/income statements). I want this code to be able to work for workbooks anyone creates but has the same basic information. This means I need to find start and end of the data on each sheet because it is not always in the same place (someone might start from "A1" and someone else from "B4", etc).

I've looked on the many websites for different ways to locate the first row used and last column used. What I have so far sometimes locates the starting row, ending row, starting column, and ending column correctly and other times it doesn't.

Sub FormatWorkbook()
Dim ws As Worksheet
Dim rowStart As Long
Dim columnStart As Long
Dim rowEnd As Long
Dim columnEnd As Long
Dim printStart As String
Dim printEnd As String

Application.ScreenUpdating = False

'Turn off print communication
Application.PrintCommunication = False
'Loop through sheets
For Each ws In Worksheets
    'Make current sheet activesheet
    ws.Select
    'Set rowStart, columnStart, rowEnd, and columnEnd to the used range
    With ActiveSheet
        rowStart = .Cells.Find(what:="*", after:=.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=False).Row
        columnStart = .Cells.Find(what:="*", after:=.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False).Column
        rowEnd = .Cells.Find(what:="*", after:=.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False).Row
        columnEnd = .Cells.Find(what:="*", after:=.Range("A1"), LookAt:=xlPart, LookIn:=xlValues, searchorder:=xlByColumns, searchdirection:=xlPrevious, MatchCase:=False).Column
    End With

This is just a portion of the program but the one I am most confused about. If anyone could help out I would really appreciate it. Also, if there is a better way to accomplish this task I'm all ears. The rest of my code is below for reference.

'Insert or Delete Space above the first used row
    If rowStart < 4 Then
        Do While rowStart < 4
            Range("1:1").Select
            Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            ws.Select
            With ActiveSheet
                rowStart = .Cells.Find(what:="*", after:=.Range("A1"), LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlNext).Row
            End With
        Loop
    ElseIf rowStart > 4 Then
        Do While rowStart > 4
            Range("1:1").Select
            Selection.Delete Shift:=xlUp
            ws.Select
            With ActiveSheet
                rowStart = .Cells.Find(what:="*", after:=.Range("A1"), LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlNext).Row
            End With
        Loop
    End If

    'I think I need to adjust the columnStart, rowEnd, and columnEnd values after inserting and deleting rows
    ws.Select
    printStart = ActiveSheet.Cells(1, columnStart).Address
    printEnd = ActiveSheet.Cells(rowEnd, columnEnd).Address

    'Format headers, footers, and set the print area
    ActiveSheet.PageSetup.CenterHeaderPicture.Filename = _
        "\\antilles\MyDocs\xxxx\My Documents\My Pictures\xxxx.png"
    With ActiveSheet.PageSetup
        .CenterHeader = "&G"
        .LeftFooter = "&""Palatino Linotype,Regular""&F"
        .CenterFooter = "&""Palatino Linotype,Regular""Prepared By: xxxx"
        .RightFooter = "&""Palatino Linotype,Regular""&D"
        .PrintArea = printStart & ":" & printEnd
    End With

Next ws
Application.PrintCommunication = True

End Sub

Upvotes: 1

Views: 2427

Answers (2)

klausnrooster
klausnrooster

Reputation: 560

I've done a lot of this and I recommend simple crude methods. Without coding a whole thing, but rather hinting...

dim iRow as integer
For iRow = 1 to ...
    cells(iRow,65000).end(xlup).select
    if activecell.row = 1 then
         activecell.entirecolumn.delete
         exit For
    endif
next iRow

As much as possible force some structure on the sheets. Then go see http://www.ozgrid.com/VBA/ExcelRanges.htm When googling, include

ozgrid|Pearson|Tushar|"Mr. Excel"|Erlandsen|Peltier|dailydoseofexcel

in the search string.

Upvotes: 1

Tony Dallimore
Tony Dallimore

Reputation: 12413

Excel VBA offers a selection of techniques for finding the first or last used row or column but none work in every situation.

Some problems with your code:

  • If the worksheet contains something, Find returns a range. A range has a row so your statements will work. But if the worksheet is empty, Find returns Nothing. You must use Set Rng = .Cells.Find ... then test Rng to not be Nothing before accessing Rng.Row or Rng.Column. See the code I reference below if this is not clear.

  • Note the after in After:=.Range("A1"). Find does not examine .Range("A1") until it has searched every other cell and wraps back to the beginning. In any example in which the start point is .Range("A1") the search direction will be xlPrevious so it immediately wraps and starts searching from the bottom right cell. Try After:=.Cells(Rows.Count, Columns.Count) when the search direction is xlNext.

There was an earlier question that was similar to yours. I posted some code which showed a selection of techniques for finding the last row or column and the situations in which they fail. I suggest you visit that answer and try the code: https://stackoverflow.com/a/18220846/973283.

Good luck and happy VBA programming.

Upvotes: 0

Related Questions