Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
missing rows
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
missing rows
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 "Rodica" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
missing rows
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 "Rodica" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
missing rows
"Bernard Liengme" wrote: 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 "Rodica" wrote in message ... 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! 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Missing 3 rows | Excel Discussion (Misc queries) | |||
Missing Rows? | Excel Discussion (Misc queries) | |||
Missing rows ? | Excel Worksheet Functions | |||
Add missing rows | Excel Worksheet Functions | |||
Missing rows! | Excel Discussion (Misc queries) |