VBA代码调试求助：CSV数据排序及动态可见单元格范围复制失败

Let's break down the problems in your code and fix them step by step:

Key Issues in Original Code #

• Incorrect loop range for hiding rows: Your For Each Cell In ws2.Range("A1").End(xlDown) only targets a single cell, not the entire column of data.
• Wrong way to get last visible row/column: SpecialCells(xlCellTypeVisible).Row returns the first visible row, not the last one.
• Invalid range syntax: ws2.Range(StartCell.ws2.Cells(LastRow, LastColumn)) has a typo (extra ws2. inside) and doesn't properly define the range.
• Unreliable use of Activate/Select: These can cause errors if workbook focus shifts, and are unnecessary.
• No handling for empty visible ranges: If no rows match your criteria, the code will throw an error.

Corrected Code #

Sub Get_OA_Data()
    'Find OA data from source SQL file and copy into serial number generator
    Dim ws As Worksheet
    Dim wkb2 As Workbook
    Dim ws2 As Worksheet
    Dim rng As Range
    Dim rng2 As Range
    Dim LastRow As Long
    Dim LastVisibleRow As Long
    Dim oaNumber As String
    
    'Set workbooks and worksheets
    Set ws = ThisWorkbook.Worksheets("Data Entry")
    Set rng2 = ws.Range("F6")
    oaNumber = rng2.Value 'Store OA number for reuse
    
    'Clear previous serial number error messages
    ws.Range("I6:I7").ClearContents
    
    'Open CSV file
    Set wkb2 = Workbooks.Open("\\srvabdotfpr08\PC_APPS\forum\Gateshead Serialisation\sys_serialisation1.csv")
    Set ws2 = wkb2.Worksheets("sys_serialisation1")
    
    'Unhide all rows first (in case previous runs left rows hidden)
    ws2.Rows.Hidden = False
    
    'Hide rows that don't match the first 6 characters of the OA number
    LastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row 'Get last row with data in column A
    For Each Cell In ws2.Range("A1:A" & LastRow)
        If Left(Cell.Value, 6) <> oaNumber Then
            Cell.EntireRow.Hidden = True
        End If
    Next Cell
    
    'Get last visible row in column A (handles non-contiguous visible ranges)
    On Error Resume Next 'Suppress error if no visible rows
    LastVisibleRow = ws2.Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).Areas( _
        ws2.Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).Areas.Count).Row
    On Error GoTo 0 'Reset error handling
    
    'Only proceed if there are visible rows
    If LastVisibleRow > 0 Then
        'Define the visible range (columns A-F, rows 1 to last visible row)
        Set rng = ws2.Range("A1:F" & LastVisibleRow).SpecialCells(xlCellTypeVisible)
        
        'Copy values to target sheet (no need for Activate/Select)
        rng.Copy
        ws.Range("D12").PasteSpecial Paste:=xlPasteValues
    Else
        'Optional: Notify user no matching data was found
        ws.Range("I6").Value = "No matching OA data found"
    End If
    
    'Close CSV file without saving changes
    wkb2.Close SaveChanges:=False
End Sub

What Changed & Why #

1. Fixed row hiding loop: We first get the actual last row of data in column A, then loop through every cell from A1 to that row. We also unhide all rows first to avoid leftover hidden rows from previous runs.
2. Reliable last visible row detection: Using Areas.Count lets us target the last block of visible rows, even if there are gaps. Error handling prevents crashes if no rows match.
3. Correct range definition: We explicitly target columns A-F (1 to 6) as you requested, and use SpecialCells(xlCellTypeVisible) to only include visible cells.
4. Removed Activate/Select: Directly referencing ranges is more stable and faster.
5. Added error handling for no matches: The code now notifies you if no rows match your OA number instead of throwing an error.
6. Closed the CSV file: We added wkb2.Close SaveChanges:=False to clean up after processing, so you don't have leftover open workbooks.

Give this revised code a try—it should resolve both your sorting/hiding and dynamic range issues.

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