背景
- Data页中是原始数据，Test页中的前三行是使用excel中的公式：
=IF(ISBLANK(Data!A1:A298), "", Data!A1:A298)
=IF(ISBLANK(Data!B1:B298), "", Data!B1:B298)
=IF(ISBLANK(Data!C1:C298), "", Data!C1:C298)
- 由于Test页中的前三列是直接从Data页中映射过来的，因此Data页中如果有增删改查，Test页中的前三列是可以自动同步的。
- 但是Test页中的第四，五，六...列是用户直接在Test页中自己配置的，这几列就无法实现同步了，如果用户直接在Data页新增或删除一行就会造成行数的错位，在网上查了各种方法无法实现其余列的同步，所以就借用VBA代码实现了，分享给大家
实现思路
- 删除
- 若想删除的话，就需要知道用户在Data页中操作的是哪一行，获取到行之后，再在Test页中删除对应的行即可。
- 获取用户操作的行数，写在Worksheet_SelectionChange函数中，并且需要定义一个全局变量，方便在进行删除操作的时候使用：
Dim DeletedRow As Long ' 用于存储被删除的行号
Dim SelectedRows As Collection ' 如果是选了多行就存储多行的行号
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' 如果不是选择的整行就退出
If Target.Columns.Count <> Me.Columns.Count Then
Exit Sub
End If
If Target.Rows.Count = 1 Then
' 记录行号
DeletedRow = Target.row
Else
' 存储选中的行号到列表中
Dim cell As Range
For Each cell In Target.Rows
SelectedRows.Add cell.row
Next cell
End If
End Sub
- 删除操作直接写在Worksheet_Change函数中
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsData As Worksheet
Dim wsTest As Worksheet
Dim i As Long
' 设置工作表
Set wsData = Worksheets("Data")
Set wsTest = Worksheets("Test")
' delete row in wsTest
If DeletedRow > 0 Then
wsTest.Rows(DeletedRow).Delete
DeletedRow = 0
End If
If Not SelectedRows Is Nothing Then
Dim rowNum As Variant
For Each rowNum In SelectedRows
wsTest.Rows(rowNum).Delete
Next rowNum
Set SelectedRows = Nothing
End If
End Sub
- 新增
删除的行同步实现了之后，继续实现新增的操作，由于新增的行是空行，所以可以根据这个特点，来确定新增的行号
' 获取 Data 页的最后一行
lastRowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRowData
' 检查是否增加的为一个空行
If wsData.Cells(i, 1).Value = "" Then
wsTest.Rows(i).Insert Shift:=xlDown
' 记得要把DeletedRow置0，防止影响删除操作
DeletedRow = 0
End If
Next i
完整代码如下：
Dim DeletedRow As Long ' 用于存储被删除的行号
Dim SelectedRows As Collection ' 如果是选了多行就存储多行的行号
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' 如果不是选择的整行就退出
If Target.Columns.Count <> Me.Columns.Count Then
Exit Sub
End If
If Target.Rows.Count = 1 Then
' 记录行号
DeletedRow = Target.row
Else
' 存储选中的行号到列表中
Dim cell As Range
For Each cell In Target.Rows
SelectedRows.Add cell.row
Next cell
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsData As Worksheet
Dim wsTest As Worksheet
Dim lastRowData As Long
Dim i As Long
' 设置工作表
Set wsData = Worksheets("Data")
Set wsTest = Worksheets("Test")
' 获取 Data 页的最后一行
lastRowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRowData
' 检查是否增加的为一个空行
If wsData.Cells(i, 1).Value = "" Then
wsTest.Rows(i).Insert Shift:=xlDown
DeletedRow = 0
Set SelectedRows = Nothing
End If
Next i
' delete row in wsTest
If DeletedRow > 0 Then
wsTest.Rows(DeletedRow).Delete
DeletedRow = 0
End If
If Not SelectedRows Is Nothing Then
Dim rowNum As Variant
For Each rowNum In SelectedRows
wsTest.Rows(rowNum).Delete
Next rowNum
Set SelectedRows = Nothing
End If
End Sub
