问题描述
我有一个报告,我每天拉扯一个非常尴尬的格式。根据每位员工的姓名,它包含4列列组成的非正式表的变量行数。我所拥有的是B列中的员工姓名,前面有2个空白行,后面跟着1个空白行数据。我想要完成的是循环遍历数据,识别列B>空白的单元格,删除该单元下面的整个2行,并删除整个1在那个单元格之上。
以下是我到目前为止。不太多:
Sub test()
Dim currentSht As Worksheet
Dim startCell As范围
Dim lastRow As Long
Dim lastCol As Long
Dim i as integer
设置currentSht = ActiveWorkbook.Sheets(1)
设置startCell = currentSht.Range(A1)
lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column
对于i = lastRow到1
如果Cells(i,B)。值<> 然后
结束子
,而不会对代码进行重大更改,请尝试:
对于i = lastRow到1 Step - 1
如果单元格(i,B)。值<> 然后
范围(单元格(i,B)。偏移量(1),单元格(i,B)。偏移量(2))EntireRow.Delete' (我,B)。偏移(-1).EntireRow.Delete'删除一个以上
你已经到了你的非空白单元格(即 Cells(i,b)
)。要引用与您已有的单元格相关的范围,请使用 OFFSET
。
所以,按照这个顺序,您可以从单元格 Offset(1)
之下的一个单元格中选择一个单元格范围,位于 Offset(2))之下的两个单元格中。将这个范围更改为
ENTIREROW`,然后删除。
然后选择上方的单元格Offset( - 1)
,选择 ENTIREROW
并删除。
I have a report that I pull everyday that is placed in a very awekward format. It's contains a variable row count by 4 columns organized into unofficial tables based on the Name of each employee.
What I have is an employee name in column B preceded 2 blank rows above and followed by 1 blank row of data below.
What I want to accomplish is loop through the data, identify cells in column B <> blank, delete the entire 2 rows below that cell, and delete the entire 1 row above that cell.
Below is what I have so far. not much:
Sub test()
Dim currentSht As Worksheet
Dim startCell As Range
Dim lastRow As Long
Dim lastCol As Long
Dim i as integer
Set currentSht = ActiveWorkbook.Sheets(1)
Set startCell = currentSht.Range("A1")
lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column
For i = lastRow To 1
If Cells(i, "B").Value <> "" Then
End Sub
without making major changes to your code, try this:
For i = lastRow To 1 Step - 1
If Cells(i, "B").Value <> "" Then
Range(Cells(i, "B").Offset(1), Cells(i, "B").Offset(2)).EntireRow.Delete 'delete two below
Cells(i, "B").Offset(-1).EntireRow.Delete ' delete one above
You already get to your non-blank cell (ie Cells(i,"b")
). To reference a range in relation to a cell you already have, use OFFSET
.
So, and in this order, you select a range of cells from one below your cell Offset(1)
to two cells below Offset(2)'. Change this range to
ENTIREROW` for those cells, and delete.
Then you select the cell above Offset(-1)
, select the ENTIREROW
and delete.
这篇关于Excel VBA - 基于标准删除行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!