对象“_Global”的方法“范围”失败。错误 [英] Method 'Range' of object '_Global' failed. error

问题描述

我试图让Excel找出工作表的哪些列是空白的。最终的想法是让它删除完全空白的列。
这是我到目前为止的代码：

  Sub Macro2（）
'
'宏2宏
'
 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&我& ：& C& j 
如果Range（location）.Select =then 
 numNull = numNull + 1 
 End If 
 Next j 
如果numNull = totalRows  -  1 Then 
 MsgBox（Column& i&is null）
 End If 
 Next i 
 
 End Sub 
  

最后，它检查以查看 numNull （行中的空条目数）= totalRows减去标题。直到语句 If Range（location）.Select =。现在编译器说：

对象'_Global'的方法'范围'失败

有人知道这是什么意思还是如何解决？

解决方案

你的代码使用。当您使用 .Value



时，请选择 p>这可能会更快：

  Sub Tester（）
 
 Dim col As Range，ur As Range 
 Dim numRows As Long，numCols As Long，i As Long，awf 
 
 Set awf = Application.WorksheetFunction 
设置ur = ActiveSheet.UsedRange 
 
 numRows = ur.Rows.Count 
 numCols = ur.Columns.Count 
 
'编辑：帐号行... 
设置ur = ur.Offset（1 ，0）.Resize（numRows-1，numCols）
 numRows = numRows  -  1 
 
对于i = numCols到1步-1 
设置col = ur.Columns ）
如果awf.CountBlank（col）= numRows然后
 MsgBo xColumn＃& col.Column& 是空的
'col.EntireColumn.Delete 
 End If 
 Next i 
 
 End Sub 
  

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?

解决方案

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

这篇关于对象“_Global”的方法“范围”失败。错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！