Excel VBA - 基于标准删除行 [英] Excel VBA - Delete Rows Based on Criteria

问题描述

我有一个报告，我每天拉扯一个非常尴尬的格式。根据每位员工的姓名，它包含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 toENTIREROW` for those cells, and delete.

Then you select the cell above Offset(-1), select the ENTIREROW and delete.

这篇关于Excel VBA - 基于标准删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！