Question

我在Excel有一系列。与此类似:


a	b	c	d
a	b	c	d	e	f
a	b	c	d	e	f	g	h	i	j	k	l
a	b	c	d	e	f	g	h

我怎么能像现在这样获得每行最后一栏(此处为N=2)?


c	d
e	f
k	l
g	h

I tried the following:

Sub keep_last_n_columns()

Dim selectedRange As Range
Dim numColumnsToKeep As Integer
Dim lastColumn As Integer

  Prompt user to select a range
On Error Resume Next
Set selectedRange = Application.InputBox("Select a range:", Type:=8)
On Error GoTo 0

  Check if a range is selected
If selectedRange Is Nothing Then
    MsgBox "No range selected. Macro canceled.", vbExclamation
    Exit Sub
End If

  Prompt user to enter the number of columns to keep
On Error Resume Next
numColumnsToKeep = Application.InputBox("Enter the number of columns to keep for each row:", Type:=1)
On Error GoTo 0

  Check if a valid number is entered
If numColumnsToKeep <= 0 Then
    MsgBox "Invalid number of columns. Macro canceled.", vbExclamation
    Exit Sub
End If

  Loop through each row in the selected range
For i = 1 To selectedRange.Rows.Count
  Find the last column with data in the current row
lastColumn = selectedRange.Cells(i, selectedRange.Columns.Count).End(xlToLeft).Column

  Keep only the specified number of columns
If lastColumn > numColumnsToKeep Then
    selectedRange.Rows(i).Resize(, lastColumn - numColumnsToKeep).Delete Shift:=xlToLeft
End If
Next i

End Sub

It is keeping the last n rows of the biggest row and deleting the rest.

Answer 1

你的法典问题是,你正在研究每一行的方方面面,包括空洞的囚室。

该法典使用工作单功能(CountA(假设一系列的价值观)对非豁免电池进行估算,然后将牢房转作删除:

Public Sub keepLastNColumns(rg As Range, n As Long)
Dim r As Range
Dim cntCells As Long, i As Long

For Each r In rg.Rows
    cntCells = WorksheetFunction.CountA(r)
    r.Resize(, cntCells - n).Delete xlShiftToLeft
Next

End Sub

You can call this sub like this: keepLastNColumns selectedRange, numColumnsToKeep

Answer 2

法典存在一个小问题。最长的排位和选任仅到该行结束,由于最终办法,最终定夺了第1栏。因此,如上表所示,在计算一个起点栏值时加油。

lastColumn = selectedRange.Cells(i, selectedRange.Columns.Count + 1).End(xlToLeft).Column

友情链接