Method 'Range' of object '_Global' failed. error

Question

I'm trying to get Excel to figure out which columns of a worksheet are blank. Eventually the idea is to get it to delete the completely blank columns. Here's the code I have so far:

Sub Macro2()
'
' Macro2 Macro
'
Dim totalCols As Integer
Dim totalRows As Integer

totalCols = ActiveSheet.UsedRange.Columns.Count
totalRows = ActiveSheet.UsedRange.Rows.Count

Dim i As Integer
Dim j As Integer
Dim numNull As Integer

For i = 1 To totalCols
    For j = 2 To totalRows
        Dim location As String
        location = "R" & i & ":" & "C" & j
        If Range(location).Select = "" Then
            numNull = numNull + 1
        End If
    Next j
    If numNull = totalRows - 1 Then
        MsgBox ("Column " & i & "is null")
    End If
Next i

End Sub

At the end it checks to see if numNull (number of null entries in the row) = totalRows minus the header. I had it working up until the statement If Range(location).Select = "". Now the compiler says:

Method 'Range' of object '_Global' failed

Does anyone know what this means or how I can fix it?

Should location not be location = "R" & i & ":C" & j alas with a colon in between ? — oneindelijk
– oneindelijk, Commented Jul 11, 2013 at 12:21
@oneindelijk yeah i believe so, that would make it a proper excel range. will edit accordingly. — franklin
– franklin, Commented Jul 11, 2013 at 13:50

Tim Williams · Accepted Answer · 2011-10-13 04:18:07Z

5

Your code is using .Select when you should be using .Value

This might be faster though:

Sub Tester()

    Dim col As Range, ur As Range
    Dim numRows As Long, numCols As Long, i As Long, awf

    Set awf = Application.WorksheetFunction
    Set ur = ActiveSheet.UsedRange

    numRows = ur.Rows.Count
    numCols = ur.Columns.Count

    'edit: account for header row...
    Set ur = ur.Offset(1,0).Resize(numRows-1, numCols)
    numRows = numRows - 1

    For i = numCols To 1 Step -1
        Set col = ur.Columns(i)
        If awf.CountBlank(col) = numRows Then
            MsgBox "Column #" & col.Column & " is empty"
            'col.EntireColumn.Delete
        End If
    Next i

End Sub

edited Oct 13, 2011 at 4:18

answered Oct 13, 2011 at 0:06

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

franklin Over a year ago

hmm...this doesn't seem right. because now its only returning the columns that have ALL entries filled..

dmogle · Accepted Answer · 2011-10-13 02:19:14Z

2

At this point 'Range' doesn't refer to an instantiated object. The Range class can't be used statically this way - you have to instantiate a Range object, usually by calling an appropriate object's factory method. There are probably other ways to do it, but I generally call ActiveSheet.Range.

As noted by Tim Williams above, you should probably be using .Value rather than .Select; I'm not at all sure what .Select returns, the MSDN reference just says data type variant.

I'm not at a Windows machine to test, but try:

If ActiveSheet.Range(location).Value = "" Then

MSDN Range Object Reference

answered Oct 13, 2011 at 2:19

dmogle

3112 silver badges4 bronze badges

1 Comment

franklin Over a year ago

i think the keyword .Select literally selects a range of cells in a document. if for example you say: ActiveSheet.Range(SomeRange).Select vba will highlight the values located in SomeRange

Collectives™ on Stack Overflow

Method 'Range' of object '_Global' failed. error

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related