怎麼在Excel匯總表中,刪除標記顏色以外的表格(包括各明細表)?
不得不說,你這個問題有點難,其實也不是難,是噁心。對於一般的沒有VBA基礎的人,處理這個問題就是通過一步步繁複的操作來實現。
如果說通過篩選去找,數據量大的話,這個事無疑會噁心死人。
我看這個問題在這有一段時間了,但是也沒人給出靠譜的回答,所以特地寫了一段代碼。經測試,完全沒有問題。
而且,這個問題提的也很模糊,不太方便給出針對性的做法
對於寫VBA代碼來說沒有說清楚的地方有以下幾處:
表格中標記的顏色是統一的顏色還是有多種不同的顏色
表格中被標記顏色的地方是對數據區域整行標記還是僅對單元格標記
問題中所提到的匯總表和明細表是不是在同一個工作簿中
刪除標記顏色以外的表格是單元格刪除還是整行、整列、或者整sheet刪除
有標記顏色但是空白單元格怎麼處理
大概有多少的sheet需要處理,有沒有無標記顏色的sheet,有沒有空白sheet
如果是刪除標記顏色以外的單元格,那麼如果出現空白行或列,要不要一併刪除
以上情況都是編寫VBA代碼需要考慮的地方
我這裡呢,根據給出的模糊問題,簡單的寫了一個大概能用的宏命令
先說一下我的思路:假設所有表格是在同一個工作簿中
統計出一共有多少個工作博,用於做循環查詢
找出每個sheet中沒有被標記顏色的單元格,並清除單元格格式及內容
如果有未標記顏色且無任何單元格內容的表格,則給出提示,並結束循環
如果需要刪除內容之前的空白行或列,則刪除整行、整列
下圖是我做的實例,有三個Sheet表,每個表格中存在標註了顏色的行,或單元格:
Sheet1,標題行標註顏色,數據中整行包含不同顏色
Sheet2,標題行標註顏色,數據中非整行包含不同顏色
Sheet3,標題行未標註顏色,數據中非整行包含不同顏色
VBA編輯器打開方法,快捷鍵:Alt+F11,工程區,插入,模塊
VBA代碼圖示(源碼佔用篇幅較大,我放到最後了):
其中
Sub Clear():用於清除未標註顏色的非空單元格
Sub DeleteEmptyRows():用於刪除空行
Sub DeleteEmptyColumns():用於刪除空列
代碼執行過程中,只執行一個工程,但通過Sub Clear()工程,調用了Sub DeleteEmptyRows()和Sub DeleteEmptyColumns()
我們來看一下執行代碼的效果:
代碼執行時,為了看效果,我屏蔽了兩條返回sheet1的代碼
從動圖可以看到,代碼執行的很快,也達到了我們的目
清楚了所有未標記顏色的單元格
清楚了數據中的冗餘空行
我再一步一步執行代碼,給大家看一下Excel都幹了些什麼(由於多個sheet處理機制一樣,這裡只錄製了兩個sheet的處理過程慢放):
通過慢放,大家應該能看出,代碼真的是很蠢的,它也是一個一個的單元格去刪除,然後再去刪除行。
需要注意的是,如果表格中存在空的sheet,vba會給出一個提示,如果空表夾雜在有數據的表格中間,那麼代碼運行到空表的時候會退出,直接給出提示,不再向下運行。
提示如下:
sheet4為一個空的表格
對於宏,你也可以再Excel中插入一個控制項,指定到所編寫的宏,之後,點擊控制項即可執行宏了。操作方法如下:
ok,就這麼多吧,代碼我寫在下邊了,沒有寫注釋,如果有感興趣的朋友可以自己去研究優化一下,或者有什麼疑問,評論或私信聯繫我即可:
橫線中間為VBA代碼:
--------------------------------------
Sub Clear()
Dim rng As Range, i As Integer
For i = 1 To ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets(i).Select
For Each rng In ActiveSheet.UsedRange.SpecialCells(2)
On Error GoTo Skip
If rng.Interior.ColorIndex = xlNone Then
rng.Clear
End If
Next
Call DeleteEmptyRows
Call DeleteEmptyColumns
Next
ActiveWorkbook.Worksheets(1).Select
Exit Sub
Skip:
ActiveWorkbook.Worksheets(1).Select
MsgBox "已經沒有未標記顏色的非空單元格"
End Sub
Sub DeleteEmptyRows()
Dim LastRow As Integer, r As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
For r = LastRow To 1 Step -1
If WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub
Sub DeleteEmptyColumns()
Dim LastColumn As Integer, c As Integer
LastColumn = ActiveSheet.UsedRange.Columns.Count
LastColumn = LastColumn + ActiveSheet.UsedRange.Column
For c = LastColumn To 1 Step -1
If WorksheetFunction.CountA(Columns(c)) = 0 Then
Columns(c).Delete
End If
Next c
End Sub
--------------------------------------
方法很多。我可以說下自己的想法。首先可以在「數據」菜單中進行全excel篩選,然後點擊任意列的下拉按鈕,選擇按顏色篩選,選出填充顏色的行,然後對篩選出來(符合要求)的行進行刪除操作。
推薦閱讀:
※這幾個高能函數,你的Excel里有沒有?
※6個含金量100%的Excel圖片實用技巧解讀!
※Excel函數公式:使用Offset函數製作動態圖表
※EXCEL中一列(行)轉多行多列或多行多列轉一列(行)
※如何製作Excel水印?
TAG:Excel |