| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete and Move Up
Hi...
I have 3000 records. In column B, some cells are reading #N/A. I need some code that will delete the rows where the #N/A occours and leave no gaps. So by the time I'm through I have say 2800 records with no gaps in the rows? Is this possible? Thanks Gordon |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete and Move Up
On your active row try this.... Selection.Delete Shift:=xlUp Louis Markham |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete and Move Up
Hi Gordon,
If no other error present, the you can use (save before testing) Columns("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete Else loop, delete the N/A then Columns("B:B").SpecialCells(xlblanks).EntireRow.De lete Regards JY "Gordon" wrote in message ... Hi... I have 3000 records. In column B, some cells are reading #N/A. I need some code that will delete the rows where the #N/A occours and leave no gaps. So by the time I'm through I have say 2800 records with no gaps in the rows? Is this possible? Thanks Gordon |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete and Move Up
Jean...
I'm not too hot on the rules that govern the building of workable macros, though I know how they work when I see them - bit like learning a foreign language. Could you wrap this up for me so it will work. Thanks in advance. Gordon... "Jean-Yves" wrote: Hi Gordon, If no other error present, the you can use (save before testing) Columns("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete Else loop, delete the N/A then Columns("B:B").SpecialCells(xlblanks).EntireRow.De lete Regards JY "Gordon" wrote in message ... Hi... I have 3000 records. In column B, some cells are reading #N/A. I need some code that will delete the rows where the #N/A occours and leave no gaps. So by the time I'm through I have say 2800 records with no gaps in the rows? Is this possible? Thanks Gordon |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete and Move Up
sub test
This use the xl function IsNA, if true, clear cell content Dim rng as range for each rng in range("B2",range("B2")).end(xldown) if application.WorksheetFunction.IsNA(range) then rng.clear next Columns("B:B").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete end sub Nice WE (*I go home now*) Regards JY "Gordon" wrote in message ... Jean... I'm not too hot on the rules that govern the building of workable macros, though I know how they work when I see them - bit like learning a foreign language. Could you wrap this up for me so it will work. Thanks in advance. Gordon... "Jean-Yves" wrote: Hi Gordon, If no other error present, the you can use (save before testing) Columns("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete Else loop, delete the N/A then Columns("B:B").SpecialCells(xlblanks).EntireRow.De lete Regards JY "Gordon" wrote in message ... Hi... I have 3000 records. In column B, some cells are reading #N/A. I need some code that will delete the rows where the #N/A occours and leave no gaps. So by the time I'm through I have say 2800 records with no gaps in the rows? Is this possible? Thanks Gordon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting every other row to delete or move | Excel Discussion (Misc queries) | |||
| How do I set up an automatic delete or move of a row of data? | Excel Discussion (Misc queries) | |||
| Button to move row and delete old row | Excel Programming | |||
| Move text after delete | Excel Programming | |||
| Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming | |||
![Gordon[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
