Excel VBA问题：从活动单元格起选F:H列数据并选择性粘贴失败

Let's break down what's going wrong with your current code and fix it to match your requirement: selecting columns F, G, H starting from the active cell's row (not row 1) down to the last row with data in those columns.

What's Wrong with Your Original Code? #

• You assigned currentcell = ActiveCell but never used the row number of this cell to set your starting range.
• The line LastRow = Cells(Rows.Count, "F" & currentcell).End(xlUp).Row is invalid: "F" & currentcell creates a malformed column reference (e.g., if your active cell is A5, this becomes FA5, which isn't a valid column).

Corrected Code #

Here's the revised code that does exactly what you need:

Sub selecting_range()
    Dim rng As Range
    Dim currentRow As Long
    Dim LastRowF As Long, LastRowG As Long, LastRowH As Long
    Dim LastRow As Long
    
    ' Get the row number of the active cell
    currentRow = ActiveCell.Row
    
    ' Calculate the last row with data for each of columns F, G, H
    LastRowF = Cells(Rows.Count, "F").End(xlUp).Row
    LastRowG = Cells(Rows.Count, "G").End(xlUp).Row
    LastRowH = Cells(Rows.Count, "H").End(xlUp).Row
    
    ' Use the maximum last row across the three columns to ensure we cover all data
    LastRow = Application.Max(LastRowF, LastRowG, LastRowH)
    
    ' Set the range from active cell's row in column F to last row in column H
    Set rng = Range("F" & currentRow & ":H" & LastRow)
    
    ' Select the range (you can add your PasteSpecial code here)
    rng.Select
    
    ' Example: If you want to perform PasteSpecial (e.g., paste values), uncomment below
    ' rng.Copy
    ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ' Application.CutCopyMode = False
End Sub

Key Improvements #

• We capture the active cell's row number to use as our starting point instead of hardcoding row 1.
• We calculate the last row for each target column separately, then take the largest value to make sure we don't miss any data in F, G, or H.
• The range is correctly defined using the starting row and final row across all three columns.

Optional: Directly Add PasteSpecial #

If you want to skip manually selecting and run the PasteSpecial operation right after defining the range, you can integrate the paste logic directly (the commented lines in the code above show how to paste values as an example).

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