Remove extraneous rows and columns and reset the last cell : UsedRange « Access « VBA / Excel / Access / Word

Home
VBA / Excel / Access / Word
1.Access
2.Application
3.Data Type
4.Data Type Functions
5.Date Functions
6.Excel
7.File Path
8.Forms
9.Language Basics
10.Math Functions
11.Outlook
12.PowerPoint
13.String Functions
14.Windows API
15.Word
16.XML
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
VBA / Excel / Access / Word » Access » UsedRange 
Remove extraneous rows and columns and reset the last cell
 
     Sub DeleteUnusedFormats()
         Dim lLastRow As Long, lLastColumn As Long
         Dim lRealLastRow As Long, lRealLastColumn As Long
         With Range("A1").SpecialCells(xlCellTypeLastCell)
             lLastRow = .Row
             lLastColumn = .Column
         End With
         lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas,, xlByRows, xlPrevious).Row
         lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas,, _
                   xlByColumns, xlPrevious).Column
         If lRealLastRow < lLastRow Then
             Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
         End If
         If lRealLastColumn < lLastColumn Then
             Range(Cells(1, lRealLastColumn + 1), _
                  Cells(1, lLastColumn)).EntireColumn.Delete
         End If
         ActiveSheet.UsedRange    'Resets LastCell
     End Sub

 
Related examples in the same category
1.Working with the Used Range
2.Loop through all used range
3.Using the SpecialCells method to select all the blanks in this range is one way to quickly fill in all the blank region cells with the region found above them:
4.Using the Areas Collection to Return a Noncontiguous Range
java2s.com  |  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.