Blue Flower

How to Select Each Nth Cells or Rows in Excel Range

I believe you're familiar with selecting in Excel, on the basis of selection in Excel I wrote on this link: Highlight and selection in Excel.

This Excel example tutorial currently you reading, you realize as a collection of examples of how all of you can select specific cells or rows in Excel.

To begin with, I will be based on the Excel option "Go To Special" with which we can select specific cells with a particular condition. In the picture below I will show how you can select all cells that contain text in a worksheet.

How to select all the cells in the worksheet containing text

In the picture above, you notice selected cells that contain text. In order to all you selecting a cell with text, do the following: HOME Tab on the Ribbon => Find & Select => Go To Special. There will be a new dialog box where you can choose several options ie. Conditions. For this example, I chose the option Constants/Text because I want on the worksheet select only cells that contain text. Of course, you see the other options that are available as conditions for selection. After selection options and clicking with the mouse on the OK button, the worksheet you see all the selected cells that contain text (cells that have the format 'Text').

 Go To Special Command for selection in Excel

How to select every Nth cell in an Excel column

In this Excel example, I'll show how you can select every Nth cell in an Excel column. eg. Selecting each 3th, 4th or 5th cell in the column. You notice it in the picture below, the selected each Nth or third cell in the column that contains the data. In the Excel example in the picture below you see that were selected every third cell in column 'A'.

 How to select every Nth cell in an Excel column

Selection of each of the third, fourth, fifth cell in the column, etc ... you can use a VBA macro that is located below. This VBA macro, you can easily customize so to edit a particular piece of code. How to copy the VBA from these web and set up in the Workbook, see the tutorial on the the respective hyperlink. This VBA copy in the 'Worksheet module' in VBE that contains data.

Sub SelectEveryNthCell()
'select every Nth cell in Column
Dim rRange As Range
Dim rEveryNth As Range
Dim lRow As Long
With Sheet1
'set column with data
Set rRange = .Range("C1", .Cells(.Rows.Count, "C").End(xlUp))
End With
'set every Nth cell, in this case 3
For lRow = 1 To rRange.Rows.Count Step 3
If lRow = 1 Then
Set rEveryNth = rRange(lRow, 1)
Else
Set rEveryNth = Union(rRange(lRow, 1), rEveryNth)
End If
Next lRow
Application.Goto rEveryNth
End Sub

How to select every third row or Nth row in the range of cells

If you need to highlight each Nth Row in a certain range of cells, then use VBA macro below. In the picture below you will see that I am using VBA selected by every 3th row in the range of cells A1: E19.

How to select every Nth Row or third row of the range 

 

The VBA macro that allows you to select every Nth Row in the cell range is below. This VBA macro copy in the your Excel VBE. Select the range of cells containing the data and then run the VBA macro. This VBA macro copy below, in the 'Worksheet module' your workbook.

Sub SelectEveryNthRow()
'Note! Before you run VBA select the Range
' Initialize ColsSelection equal to the number of columns in the selection.
ColsSelection = Selection.Columns.Count
' Initialize RowsSelection equal to the number of rows in your selection.
RowsSelection = Selection.Rows.Count
' Initialize RowsBetween equal to three.
RowsBetween = 3
' Initialize Diff equal to one row less than the first row number of the selection.
Diff = Selection.Row - 1
' Resize the selection to be 1 column wide and the same number of rows long as the initial selection.
Selection.Resize(RowsSelection, 1).Select
' Resize the selection to be every third row and the same number of columns wide as the original selection.
Set FinalRange = Selection. _
Offset(RowsBetween - 1, 0).Resize(1, ColsSelection)
' Loop through each cell in the selection.
For Each xCell In Selection
' If the row number is a multiple of 3, then . . .
If xCell.Row Mod RowsBetween = Diff Then
' ...reset FinalRange to include the union of the current
' FinalRange and the same number of columns.
Set FinalRange = Application.Union _
(FinalRange, xCell.Resize(1, ColsSelection))
' End check.
End If
' Iterate loop.
Next xCell
' Select the requested cells in the range.
FinalRange.Select
End Sub

How to Select All Unlocked Cells in the Excel worksheet

I believe you know that in the Excel Worksheet you can lock or unlock the cell and also protect cells password. In the case of on the Worksheet you have locked and unlocked cell and you want to see that all the cells unlocked then use VBA macro below. In the picture below you see some of the cells that are locked. I am entered in the unlocked cells 'Unlocked' I would show you how to Excel using VBA macros can select all an unlocked cell in the worksheet. Of course, the Worksheet is protected by a password.

How do you know that are all unlocked cells in a worksheet in Excel 

 

The VBA macro for selecting all unlocked cells in a worksheet in Excel. This VBA macro below you copy in the 'Worksheet module' your Workbook.

Sub SelectAllUnlockedCells()
'selecting every unlocked cell on Worksheet
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Else
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
If FoundCells Is Nothing Then
MsgBox "All cells are locked."
Else
FoundCells.Select
End If
End Sub

About Nth cells I wrote on the other tutorials: Copy every N-th cell of the row or column

As well as the link: Copy and Transpose each Nth Cell or Value