Wednesday, February 25, 2015

Excel VBA Removing the top row from a range

Every need a quick way to excise the header row from a range. Well this snippet will work with any range that has header.  The trick is to use the OFFSET() method of the range object.


Dim wksTemplate As Worksheet
Dim objRange As Range
Dim lngRowCount As Long

Set wksTemplate = ActiveWorkbook.Worksheets("Data")

Set objRange = wksTemplate.Cells(1, 1).CurrentRegion

        
lngRowCount = objRange.Rows.Count
Debug.Print "Before resizing " & lngRowCount
Set objRange = objRange.Offset(1, 0).Resize(objRange.Rows.Count - 1, objRange.Columns.Count)
lngRowCount = objRange.Rows.Count
Debug.Print "After resizing " & lngRowCount

No comments: