Error when using a String as Range (VBA Excel)

Question

I am trying to use a string to select some cells by using Range(MaPlage), but this doesn't work. Here is the code and how I constructed the string:

Dim MaPlage As String
Dim MyRange As Range
Dim Line As Integer
Dim AdrRD as String
Dim First_Cell As Boolean

First_Cell = True
AdrRD = "A"

For Line = 1 To 100 Step 1
If Sheet.Range(AdrRD & Line).Value = "" Then
    If First_Cell = True Then
        MaPlage = AdrRD & Line
        First_Cell = False
    Else
        MaPlage = MaPlage & ", " & AdrRD & Line
    End If
End If

Next Line

If MaPlage <> "" Then   
    Range(MaPlage).Select
    Selection.EntireRow.Delete
End If

There is an error at "Range(MaPlage).Select"

I have also tried Set MyRange = Range(MaPlage) but it gives me the same error.

Excel gives me the error in French but it's something like: "Error 1004: The 'Range' method from the object '_Global' has failed."

Thanks a lot!

EDIT: I just tried with

For Line = 1 To 40 Step 1

Instead of 100, and it worked correctly. Does this mean that when the selection goes all the way to line 100 it is to big ?

When your code halts, hover the cursor over the word MaPlage and see what the value is. Basic debugging will solve this issue for you. — SierraOscar
I often do that, and in this piece of code it looks like MaPlage is just fine! It's just that when it's too long it doesn't show the end, but that doesn't mean that the program handles it as it is shown, does it ? — LateRefrigerator

GSerg GSerg · Accepted Answer · 2015-06-29T09:10:34

It would appear maximum address length in Excel is 255 characters.

However you almost never need to build an address as a string.

Dim MyRange As Range
Dim c As Range
Dim Line As Long

For Line = 1 To 100
  Set c = Sheet.Cells(Line, "A")
  If Len(c.Value) = 0 Then
    If MyRange Is Nothing Then
        Set MyRange = c
    Else
        Set MyRange = Application.Union(MyRange, c)
    End If
  End If
Next

MyRange.EntireRow.Delete

Depending on the size of the sheet's UsedRange and your needs, you might get away with simply doing

Sheet.Range("A1:A100").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

but this can raise an error if it doesn't find any cells, and it can cause the SelectionChange event to fire for no reason (Excel bug IMO).

Error when using a String as Range (VBA Excel)

1 Answers