Error 1004 using Range.Columns - using same syntax as Range.Rows

I'm having some problems with a subroutine in VBA (Excel 2010).

The code aims to resize a block of formulas given the range object of the original block, and the new number of rows and columns. It does this by deleting excess rows/columns and filling down or the the right if needs more rows/columns.

The problem I'm having is that while the code for the rows runs just fine using the Range.Rows(...).Clear, I get an error 1004 - Application-defined or object-defined error using nearly identical code to perform the Range.Columns(...).Clear .

The Subroutine below:

Sub ResizeBlock(BlockRange As Range, Optional nRows As Variant, Optional nColumns As Variant)

If IsMissing(nRows) And IsMissing(nColumns) Then Exit Sub

Dim TopLeftCell, BottomRightCell As Range
Set TopLeftCell = BlockRange.Cells(1, 1)
Set BottomRightCell = BlockRange.Cells(BlockRange.Rows.Count, BlockRange.Columns.Count)

If Not IsMissing(nRows) Then Set BottomRightCell = BottomRightCell.Offset(nRows - BlockRange.Rows.Count, 0)
If Not IsMissing(nColumns) Then Set BottomRightCell = BottomRightCell.Offset(0, nColumns - BlockRange.Columns.Count)

Dim NewBlockRange As Range
Set NewBlockRange = Range(TopLeftCell, BottomRightCell)

Select Case BlockRange.Rows.Count - NewBlockRange.Rows.Count
    Case Is > 0
        BlockRange.Rows(NewBlockRange.Rows.Count + 1 & ":" & BlockRange.Rows.Count).Clear
    Case Is < 0
        NewBlockRange.Rows(BlockRange.Rows.Count & ":" & NewBlockRange.Rows.Count).FillDown
End Select

Select Case BlockRange.Columns.Count - NewBlockRange.Columns.Count
    Case Is > 0
        BlockRange.Columns(NewBlockRange.Columns.Count + 1 & ":" & BlockRange.Columns.Count).Clear
    Case Is < 0
        NewBlockRange.Columns(BlockRange.Columns.Count & ":" & NewBlockRange.Columns.Count).FillRight
End Select

End Sub

I am calling the subroutine using the following 'test' code:

Call ResizeBlock(Range("C5:I11"), 10, 10)

The order of the select cases doesn't matter, i.e. I still get errors in the columns part when it is above the rows part.


Well, perhaps writing out the problem made things clearer in my mind, and I seem to have solved the problem.

Solution by changing the columns select case to:

Select Case BlockRange.Columns.Count - NewBlockRange.Columns.Count
    Case Is > 0
        Range(BlockRange.Columns(NewBlockRange.Columns.Count + 1), BlockRange.Columns(BlockRange.Columns.Count)).Clear
    Case Is < 0
        Range(NewBlockRange.Columns(BlockRange.Columns.Count), NewBlockRange.Columns(NewBlockRange.Columns.Count)).FillRight
End Select

I have no idea why Range.Rows(x:y) works but Range.Columns(x:y) doesn't.


You would need to use A1 references for columns.

For example, while range.Rows("1:3") will return the first three rows of a range, you would need range.Columns("A:C") to return the first three columns.

Personally I'd do this differently, for example by using Range.Offset and Range.Resize.

Need Your Help

CodeIgniter error 404 on new install

codeigniter helper

I just installed CodeIgniter into my localhost machine. I haven't even touched anything, and when I navigate to the folder, it gives me a 404 error instead of the welcome message.