#Finding sequential numbers in excel how to
#Finding sequential numbers in excel code
#Finding sequential numbers in excel series

Kutools for Excel – 300+ Handy Features to Solve Most Excel Problems and Increase Productivity by 80%! Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. With Kutools for Excel’s Find Missing Sequence Number utility, you can quickly find and highlight the missing numbers and insert the missing numbers into the sequence list as soon as possible.

#Finding sequential numbers in excel series

Note: To highlight the non-consecutive numbers which break the series numbers, please apply this formula: =A2+1A3 in the Conditional Formatting. Then click OK > OK to close the dialogs, and now, you can see the groups of consecutive sequence numbers have been highlighted imminently. (4.) At last, choose one color you like to highlight the consecutive numbers from the Fill tab. (3.) Then click Format button to go to the Format Cells dialog box (2.) Enter this formula =A2+1=A3 ( A2 is the first number of the sequence number) into the Format values where this formula is true text box (1.) Click Use a formula to determine which cells to format in the Select a Rule Type list box In the popped out New Formatting Rule dialog box, please do the following operations: Select the break sequence number list, and click Home > Conditional Formatting > New Rule, see screenshot:Ģ. In fact, the Conditional Formatting feature of Excel can help you to highlight the consecutive or non-consecutive numbers in a column, please do as follows:ġ. Highlight non-consecutive numbers from a column with Kutools for Excel Highlight consecutive or non-consecutive numbers from a column with Conditional Formatting

#Finding sequential numbers in excel how to

Highlighting the consecutive or non-consecutive numbers from a long column may help you to identify the missing numbers in the sequence number, and this article, I will talk about how to highlight the consecutive or non-consecutive numbers in Excel? MsgBox 'No gaps found in range '' & rng.Address(0, 0) & ''.How to highlight consecutive or non-consecutive numbers from a column in Excel? MsgBox 'Found ' & k & ' gaps in range '' & rng.Address(0, 0) & ''.' '.Worksheet.Columns(.Column).ClearContents ' Write missing values from Gap Array to Destination Range.

MsgBox 'Multiple occurrences of a number detected.' ' Write missing values to (oversized) Gap Array.ĭim Gap() As Long: ReDim Gap(1 To mx - mn + 1, 1 To 1)

' Write (and count) unique values from Data Array to Unique Dictionary.ĭim dict As Object: Set dict = CreateObject('Scripting.Dictionary')

' Write values from Source Range to Data Array.ĭim mn As Long: mn = Application.Min(Data)ĭim mx As Long: mx = Application.Max(Data)

Since I learned what a gap in this case is, I thought I'll utilize this knowledge by playing around with your procedure and making a few changes (hopefully improvements).

Mx = Application.Max(a): mn = Application.Min(a)įor i = 1 To UBound(a): d(a(i, 1)) = 1: Next i Set d = CreateObject('scripting.dictionary') Set rng = Application.InputBox(Prompt:='Select a range:', _

#Finding sequential numbers in excel code

How can I fix the code so if there are no missing values a message box can say so. The code runs but if I select a column range that has no missing values I get a runtime error. Lets say I want to perform a gap analysis on 1,3,4,5,9. My goal is to perform a gap analysis in one Excel column that has consecutive numbers and have all missing values copied to a new column.Ī gap in a number analysis is a missing value in the number sequence.