背景
- Data頁中是原始數(shù)據(jù)狡门，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頁中的前三列是可以自動(dòng)同步的沪袭。
- 但是Test頁中的第四田度，五妒御，六...列是用戶直接在Test頁中自己配置的，這幾列就無法實(shí)現(xiàn)同步了镇饺，如果用戶直接在Data頁新增或刪除一行就會(huì)造成行數(shù)的錯(cuò)位乎莉，在網(wǎng)上查了各種方法無法實(shí)現(xiàn)其余列的同步，所以就借用VBA代碼實(shí)現(xiàn)了奸笤，分享給大家
實(shí)現(xiàn)思路
- 刪除
- 若想刪除的話惋啃，就需要知道用戶在Data頁中操作的是哪一行，獲取到行之后监右，再在Test頁中刪除對應(yīng)的行即可边灭。
- 獲取用戶操作的行數(shù)，寫在Worksheet_SelectionChange函數(shù)中健盒，并且需要定義一個(gè)全局變量绒瘦，方便在進(jìn)行刪除操作的時(shí)候使用：
Dim DeletedRow As Long ' 用于存儲(chǔ)被刪除的行號
Dim SelectedRows As Collection ' 如果是選了多行就存儲(chǔ)多行的行號
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
' 存儲(chǔ)選中的行號到列表中
Dim cell As Range
For Each cell In Target.Rows
SelectedRows.Add cell.row
Next cell
End If
End Sub
- 刪除操作直接寫在Worksheet_Change函數(shù)中
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsData As Worksheet
Dim wsTest As Worksheet
Dim i As Long
' 設(shè)置工作表
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
- 新增
刪除的行同步實(shí)現(xiàn)了之后，繼續(xù)實(shí)現(xiàn)新增的操作扣癣，由于新增的行是空行惰帽，所以可以根據(jù)這個(gè)特點(diǎn)，來確定新增的行號
' 獲取 Data 頁的最后一行
lastRowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRowData
' 檢查是否增加的為一個(gè)空行
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 ' 用于存儲(chǔ)被刪除的行號
Dim SelectedRows As Collection ' 如果是選了多行就存儲(chǔ)多行的行號
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
' 存儲(chǔ)選中的行號到列表中
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
' 設(shè)置工作表
Set wsData = Worksheets("Data")
Set wsTest = Worksheets("Test")
' 獲取 Data 頁的最后一行
lastRowData = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRowData
' 檢查是否增加的為一個(gè)空行
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
