如何在Excel VBA中获取UsedRange内指定列的无表头数据？

Let's break down the two issues you're facing and walk through the correct approach to get the data range in Column I (excluding the header row):

Why Your First Code Failed #

Your line cU1 = nsh.UsedRange.ListObject.ListColumns(9).DataBodyRange throws an error because you haven't converted your data into an Excel Table (ListObject). The ListObject property only exists if you've explicitly created a table (via Insert > Table), which you mentioned you haven't done. Without that table structure, trying to access ListColumns will trigger an "Object variable or With block variable not set" error.

Why Your Second Code Can Fail #

Your last-row approach is on the right track, but it has two potential pitfalls:

• Variable Type Limitation: You declared Lastrow As Integer — Excel worksheets can have up to 1,048,576 rows, which is way beyond the 32,767 limit of the Integer data type. This will cause an overflow error if your data goes past row 32767. Always use Long for row/column counts.
• Empty Data Scenario: If there's no data below the header (i.e., Lastrow = 1), Range("I2:I" & Lastrow) becomes Range("I2:I1") — Excel can't handle a range where the start row is greater than the end row, so this throws an error. Even if Lastrow = 2, if cell I2 is empty, you might still get unexpected behavior depending on how you're using cU1.

The Correct Solution #

Here's a robust code snippet that handles all scenarios (no data, single row of data, multiple rows of data) and avoids the errors:

Sub GetColumnIData()
    Dim nsh As Worksheet
    Dim Lastrow As Long
    Dim cU1 As Range
    
    ' Set your worksheet (change "Sheet1" to your actual sheet name)
    Set nsh = ThisWorkbook.Worksheets("Sheet1")
    
    ' Find the last used row in Column I (column index 9)
    Lastrow = nsh.Cells(nsh.Rows.Count, 9).End(xlUp).Row
    
    ' Check if there's data below the header (header is in row 1)
    If Lastrow > 1 Then
        ' Set the range from I2 to the last used row in Column I
        Set cU1 = nsh.Range("I2:I" & Lastrow)
        ' Optional: Verify the range (for testing)
        Debug.Print "Data range: " & cU1.Address
    Else
        ' No data below header — handle this case as needed
        Set cU1 = Nothing
        Debug.Print "No data found in Column I below the header."
    End If
End Sub

Key Improvements: #

• Uses Long for Lastrow to support all possible rows in Excel.
• Adds a check If Lastrow > 1 to avoid invalid range references when there's no data.
• Explicitly references the worksheet (nsh.Range instead of just Range) to prevent errors if another sheet is active.
• Handles the "no data" scenario gracefully by setting cU1 to Nothing (you can adjust this to fit your needs, like creating an empty range or showing a message).

内容的提问来源于stack exchange，提问作者Santhosh Arockiaxavier