How to resize a whole column?

[ForumExcel]

Excel 2010:

Following code works if I pass a rectangular range but fails if I attempt to pass an entire column; I get runtime error 1004 (Application-defined or object-defined error) in the following code.

Function GetRangeWithoutFirstNRows(inRange As Range, numRows As Integer) As Range
  Dim rowCount As Long
  Dim colCount As Long
  Dim myRange As Range
  rowCount = inRange.Rows.Count
  colCount = inRange.Columns.Count
  ' Note that an object has to always be "Set". Even a Function name if it returns an object
  Set myRange = inRange.Offset(numRows).Resize(rowCount - numRows, colCount) ' <--- Error line
  Set GetRangeWithoutFirstNRows = myRange
End Function
 
Sub TestGetRangeWithoutFirstNRows()
  Dim myRange As Range
  Set myRange = GetRangeWithoutFirstNRows(ActiveSheet.Columns(1), 1) ' not working
  PrintRangeAddress myRange
End Sub
 
Sub PrintRangeAddress(myRange As Range)
  Debug.Print myRange.address
End Sub

Thank you!

 

[Scott Huish]

You can't offset by any number of rows an entire column so you can't do that first. Also both parameters for Resize are not required. Since you aren't changing the column count, I would just leave that part out completely. There is also no need for all those variables that you only use the value once. Try this for your function:

Function GetRangeWithoutFirstNRows(inRange As Range, numRows As Integer) As Range
  ' Note that an object has to always be "Set". Even a Function name if it returns an object
  Set GetRangeWithoutFirstNRows = inRange.Resize(inRange.Rows.Count - numRows).Offset(numRows) ' <--- Error line
End Function

 

[ForumExcel]

Makes sense that one can't offset an entire column.

Thank you!