missing rows

[Guest]

hi,
my worksheet should have 1000 rows, numbered from 1 to 1000.
I only have 915 of these rows.
Could someone tell me if it is possible to determine wich of them are
missing (the missing rows are not consecutive) and eventually if Excel can
automatically insert this rows - unpopulated of course ( I work in Excel 2003
Professional)
Thanks!

 

[Bernard Liengme]

Here is a quick way to find them:
Let's assume the numbers are in A1:A915
Select A2:A915 (not A1); use Format Conditional Formatting; specify
Formula Is A2<>A1+1 and set the font or background (pattern) to red
A red cell indicates something is missing above it.

Unless you are ready to work with VBA you will need to insert the rows
manually by right clicking the row header (the number on far left of window)
and using Insert. Then type in missing number (no use Edit | Fill when you
have inserted all the missing rows)

best wishes

 

[Guest]

try this

Sub insertmissing()

'assume numbers are in column A
RowCount = 1
Do While Cells(RowCount, "A") <> "" And _
Cells(RowCount + 1, "A") <> ""

If (Cells(RowCount, "A") + 1) <> Cells(RowCount + 1, "A") Then

Cells(RowCount + 1, "A").EntireRow.Insert Shift:=xlDown
Cells(RowCount + 1, "A") = Cells(RowCount, "A") + 1

End If

RowCount = RowCount + 1

Loop

End Sub

 

[Guest]

Here is a quick way to find them:
Let's assume the numbers are in A1:A915
Select A2:A915 (not A1); use Format Conditional Formatting; specify
Formula Is A2<>A1+1 and set the font or background (pattern) to red
A red cell indicates something is missing above it.

Unless you are ready to work with VBA you will need to insert the rows
manually by right clicking the row header (the number on far left of window)
and using Insert. Then type in missing number (no use Edit | Fill when you
have inserted all the missing rows)

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email



the result is that all rows from A2 to the end turned up to red

I forgot to specify that i am a simply Excel user and not an expert so it`s
possibile to do something wrong with this formula. Anyway, thanks Bernard