跳到主要內容

如何在Excel中選擇每隔一行或第n行?

有時,當我們使用工作表時,我們需要選擇工作表的每隔一行或第n行以進行格式化,刪除或複制。 您可以手動選擇它們,但是如果有數百行,則此方法不是一個好的選擇。 這裡有一些技巧可以幫助您。

使用VBA選擇每隔一行或第n行

用Kutools for Excel選擇每隔一行或第n行好主意3


使用VBA選擇每隔一行或第n行

在此示例中,我將選擇兩個間隔的一行。 使用VBA代碼,可以完成以下操作:

1.突出顯示要每隔一行或第n行選擇的範圍。

2.Click 開發者 > Visual Basic中,一個新的 適用於應用程序的Microsoft Visual Basic 將顯示一個窗口,單擊 插入 > 模塊,然後將以下代碼輸入到模塊中:

Sub EveryOtherRow()
Dim rng As Range
Dim InputRng As Range
Dim OutRng As Range
Dim xInterval As Integer
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
xInterval = Application.InputBox("Enter row interval", xTitleId, Type:=1)
For i = 1 To InputRng.Rows.Count Step xInterval + 1
    Set rng = InputRng.Cells(i, 1)
    If OutRng Is Nothing Then
        Set OutRng = rng
    Else
        Set OutRng = Application.Union(OutRng, rng)
    End If
Next
OutRng.EntireRow.Select
End Sub

3.然後點擊 文檔乘法計算 3 按鈕運行代碼。 並彈出一個對話框供您選擇範圍。 看截圖:



4。 點擊 OK,在這種情況下,我在另一個彈出對話框中輸入3作為間隔行。 看截圖

5。 點擊 OK,並選擇了每三行。 看截圖:

您可以在第二秒中根據需要更改時間間隔 Kutoolsfor Excel 對話。


用Kutools for Excel選擇每隔一行或第n行

使用VBA代碼,您只能選擇具有指定間隔的一行,如果需要選擇具有指定間隔的兩,三或其他行,則 Excel的Kutools 可以幫助您輕鬆方便地解決此問題。

Excel的Kutools, 與以上 300 方便的功能,使您的工作更加輕鬆。 

免費安裝 Kutools for Excel,請執行以下操作:

1。 點擊 庫工具 > Select > Select Interval Rows & Columns…,請參見屏幕截圖:

doc每隔一行選擇8

2。 在裡面  Select Interval Rows & Columns 對話框,單擊文檔替換範圍名稱-7 按鈕選擇所需範圍,然後選擇 Rows or Columns Select 部分,然後在 Interval of 輸入框和 Rows 輸入框。 看截圖:

doc每隔一行選擇9

doc每隔一行選擇10

筆記:
1.如果您需要選擇選項中的每隔一行,請在“輸入間隔”框中輸入2,在“輸入間隔”中輸入1。 Rows 輸入框。

2.如果要選擇所需的整行,可以檢查 Select entire rows 選項。
doc每隔一行選擇11


使用Kutools for Excel對每隔一行或第n行進行著色

如果要在每隔一行陰影範圍內使數據看起來更加突出,如下面的屏幕截圖所示,則可以應用 Excel的KutoolsAlternate Row/Column Shading 功能以快速處理工作。

doc每隔一行選擇12
doc向下箭頭
doc每隔一行選擇13

免費安裝 Kutools for Excel,請執行以下操作:

1.選擇要間隔陰影的單元格範圍,單擊 庫工具 > Format > Alternate Row/Column Shading.
doc每隔一行選擇14

2。 在裡面 Alternate Row/Column Shading 對話框中,請執行以下操作:

1)選擇要著色的行或列;

2)選擇 Conditional formatting or 標準格式 根據需要

3)指定陰影間隔;

4)選擇陰影顏色。
doc每隔一行選擇15

3。 點擊 Ok。 現在,該範圍已在第n行中加陰影。

如果要刪除陰影,請檢查 刪除現有的備用行底紋 選項 交替的行/列底紋 對話。


相關文章:

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (12)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Help, please. This looks great, BUT when I hit the > Run button it fails with a "Compile error: Sub or Function not defined" and the code stopped at "Set rng = InputRng.Rows.Count Step xInterval +1".
This comment was minimized by the moderator on the site
Also I don't get how "Set" and "Step" could ever work together
This comment was minimized by the moderator on the site
Not sure that I will be able to help, but.. First of, there is a spacing between "C" and "ount" in "Count" when i copied your code string over to a module. Secondly, I can´t find your code anywhere else on this page, please submit the whole thing, if the problem remains.
This comment was minimized by the moderator on the site
Replace OutRng.EntireRow.Select with Intersect(OutRng.EntireRow, InputRng).Select if you want every other row within your selection
This comment was minimized by the moderator on the site
really good, thanks!
This comment was minimized by the moderator on the site
The code has one request. Does it have to select the ENTIRE ROW for each nth row? Can't it just select certain cells (range of cells) within each nth row? I don't need all the information in each row. Just certain cells of each nth row.
This comment was minimized by the moderator on the site
I made the modifications below to select 30 rows Sub EveryOtherRow() Dim rng As Range Dim InputRng As Range Dim OutRng As Range Dim xInterval As Integer xTitleId = "KutoolsforExcel" Set InputRng = Application.Selection Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8) xInterval = Application.InputBox("Enter row interval", xTitleId, Type:=1) For i = 1 To InputRng.Rows.Count Step xInterval + 1 Set rng = InputRng.Cells(i, 1).Resize(, 30) 'Resize to the number of columns to select, 30 in this case If OutRng Is Nothing Then Set OutRng = rng Else Set OutRng = Application.Union(OutRng, rng) End If Next OutRng.Select 'Use OutRng.EntireRow.Select to select the entire row End Sub
This comment was minimized by the moderator on the site
Nice, thanks, this was very helpful ^_^
This comment was minimized by the moderator on the site
fanatastic. thanks a lot.it works perfect.I have more than 7000 data that need to select them with different interval.this trick was like a miracle for me.thaaaaaaaanks.
This comment was minimized by the moderator on the site
I'm trying to use the VBA macro in Excel 2010. It will only select the first nth row, not all of them in the selection. If I run it again, it will go to the next. Any idea what I'm doing wrong? Thanks!
This comment was minimized by the moderator on the site
Just saved my ass with this code thanks! My matlab decided to randomly stop working and so I had to resort to excel which I am not as familiar with as with Matlab. This def. helped. Thanks.
This comment was minimized by the moderator on the site
I need to select multiple rows and a fixed interval and move to the other sheet please answer me as soon as possible 
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations