Moving bad rows to another sheet

Hi,

I need to move any rows containing empty fields or null or #err etc to a
separate sheet named error.  The column widths are the same and the column
headers are the same.

It seems to me as if I need to go along the lines of identifying the rows
one by one, cutting the row then moving records up.  Pasting in the row then
making sure the next one is only pasted into a row with all spaces empty.

I really dont know where to start with this so even a pointer would be a
great help!

Many Thanks

Rob


0
5/15/2004 6:06:51 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
285 Views

Similar Articles

[PageSpeed] 29

Hi
Rob as a starting point to copy records:
http://www.rondebruin.nl/copy5.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"Rob H" <quinbyNOSPAM@quinby.karoo.co.uk> schrieb im Newsbeitrag
news:oFqdnf1CKZmCxjvdSa8jmA@karoo.co.uk...
> Hi,
>
> I need to move any rows containing empty fields or null or #err etc
to a
> separate sheet named error.  The column widths are the same and the
column
> headers are the same.
>
> It seems to me as if I need to go along the lines of identifying the
rows
> one by one, cutting the row then moving records up.  Pasting in the
row then
> making sure the next one is only pasted into a row with all spaces
empty.
>
> I really dont know where to start with this so even a pointer would
be a
> great help!
>
> Many Thanks
>
> Rob
>
>

0
frank.kabel (11126)
5/15/2004 6:16:52 PM
Thanks Frank

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:eFOa3iqOEHA.2468@TK2MSFTNGP11.phx.gbl...
> Hi
> Rob as a starting point to copy records:
> http://www.rondebruin.nl/copy5.htm
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Rob H" <quinbyNOSPAM@quinby.karoo.co.uk> schrieb im Newsbeitrag
> news:oFqdnf1CKZmCxjvdSa8jmA@karoo.co.uk...
> > Hi,
> >
> > I need to move any rows containing empty fields or null or #err etc
> to a
> > separate sheet named error.  The column widths are the same and the
> column
> > headers are the same.
> >
> > It seems to me as if I need to go along the lines of identifying the
> rows
> > one by one, cutting the row then moving records up.  Pasting in the
> row then
> > making sure the next one is only pasted into a row with all spaces
> empty.
> >
> > I really dont know where to start with this so even a pointer would
> be a
> > great help!
> >
> > Many Thanks
> >
> > Rob
> >
> >
>


0
5/15/2004 6:52:53 PM
Reply:

Similar Artilces:

Name Matching
Hi when an email is sent to a miss-spelled address, how do I route these emails to a postmaster or admin account? Thanks Matt What version of Exchange? -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "Matt" <mattremoveme@edirect.co.uk> wrote in message news:006001c3b506$6c6670f0$a301280a@phx.gbl... > Hi > > when an email is sent to a miss-spelled address, how do I > route these emails to a postmaster or admin account? > > Thanks > Matt Exchange Server 2003 Running on Windows Server 2003 >----...

Row Not Found at the Subscriber
http://sanssql.blogspot.com/2010/05/row-not-found-at-subscriber-replication.html Regards, Sandesh Segu http://sanssql.blogspot.com/ ...

Messages not delivered to another Routing Group HELP NEEDED
I have three routing groups ( exchange in the 3 of them are Exch 2k sp3 ). Mail from one server ( in my headquarter routing group ) to another ( in one branch office routing group ) is getting stucked in MESSAGES WITH UNREACHABLE DESTINATION and not routed to that server. Connectors seems to be working just fine. After add my other exchange server from my headqurter group to the connector to be albe to send mail it shows the queue now in the connector but does not sends anything. Otherwise, mail from my branch office to my headquarter correctly sends mail. Any help would be gre...

Moving the Area of Interest across the Video Sequence
Hello, I am capturing a live acquisition of image 1280*1024 and defined a subwindow or Area of Interest (AOI) of size 1280*40 using MFC programming .I want to move this AOI across the 1280*1024 window (across the video sequence). I am thinking of using a timer or a for loop so that it will iterate on each of the frames of the video. Can anyone please kindly help me regarding this. Thanks in advance James. ...

moving messages from outlook express to outlook
I am trying to move messages from outlook express version 6 to outlook 2003. When I export from outlook express, there is no indication where the export file is stored. Is there a way to move messages or will I have to email them to myself? Thanks ...

How to add another user's Contacts folder to my Address Book?
Background: Exchange 2003 Server w/ Outlook 2000/2003 clients. I can add any Contacts folders in my mailbox to my Address Book by going into the Properties of the folder and checking "Show this folder as an e-mail Address Book" on the "Outlook Address Book" tab. However, if I have another user's mailbox (to which I have full permission) in my Folder List, I do not have this option in any of their Contacts folders. How do I add another user's Contacts folder, displayed in my Folder List, to my Address Book? TIA, roshan After a bit of research on SlipStick.com...

Selecting rows from various sheets #4
Cheers Fran -- sha ----------------------------------------------------------------------- shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1198 View this thread: http://www.excelforum.com/showthread.php?threadid=26665 ...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

Automatcally Hiding Rows in Excel 2000 #2
Is there a way of automatically hiding rows in Excel 2000 based on a rule (ie if there is no data in a row, we want to hide that row without doing it manually)? We have a couple of ideas which involve VB code which reacts to data within that row but if anyone has done this before or knows of a way to do it, that would be great. Thanks in advance! Tom Starr http://groups.google.com/groups?threadm=3F625B17.4BF93224%40msn.com Was a reply you got to your first post. Tom Starr wrote: > > Is there a way of automatically hiding rows in Excel 2000 based on a rule > (ie if there is no...

Inserting rows slow on large report
I have a large report I maintain with 3 data tabs and 2 report tabs, 1 for top 10 customers, about 400 rows, and another for all other customers, about 4000 rows. Every month I have to update it for any new customer/product combinations. I have optimized the formulas to the best of my abilities and gotten a full recalc time down from 3 min to 30 sec, which is acceptable. My biggest problem now is that every time I need to add a row (or delete one for that matter) it takes about 15 seconds (on the 4k row report, adding to the top 10 customers report takes 3-5 seconds), per row....

Updating 20k+ rows from Excel to access. vbscript? VBA? SQL?
Any suggestions? Some of these rows only need a few columns updated, and others need all of them updated. If possible if say row 800 column 'S' is filled in the access DB but is blank on the excel spreadsheet, if possible i like to keep the existing data in access and over right it with a blank cell. The spreadsheet is a export of the access DB, changes were made/merged from other spreadsheets so the Unique ID is the same and in column A. A script suggestions to complete this? Thanks :) dim dbe dim db dim ssql set dbe = createobj("dao.dbengine.36") set db = dbe.openda...

Excel sheet protected
Hello all I have a space in my sheet1 that is blocked but I need work in this space with a VBA macro. How can I work in this blocked space by keeping (after macro) the cells blocked? Thanks henpat You could have your macro unprotect the worksheet, do its work and then reprotect the worksheet. With worksheets("sheet99") .unprotect password:="hi" 'do your work .protect password:="hi" end with henpat wrote: > > Hello all > I have a space in my sheet1 that is blocked but I need work in this space > with a VBA macro. > How can I wo...

Has anybody else had very bad corruption?
About two weeks ago I went into MS Money like I do every day, but I noticed that my account balances were very off. As I scanned all of my accounts I noticed that all of a sudden I had alot of duplicate transactions. Not only in accounts that I autosync with (Citibank) but with accounts I maintain manually also. There was no rhyme or reason to it - and it was spread over mot of my accounts. At first I tried the salvage/super salvage utilities. No help. Then I decided to go in and just delete the dups. They were easy to spot - they were all unreconciled. I then found out that I could not delet...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

Multiple instances of a property sheet
I have two windows as part of my application, say W1 and W2. They are both derived from CFrameWnd. I have a class derived from CPropertySheet that is instantiated/displayed by both windows. When I create an instance of my property sheet (let's call it P1) from W1 (the property sheet is modal to W1) and then create another instance of my property sheet (P2) from W2 (modal to W2), I cannot close P1 by clicking OK or Cancel. Clicking OK or Cancel is completely ineffective; the dialog remains open. Then, if I close P2 by clicking OK or Cancel, both property sheets are closed. I augment CProp...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

Formatting four pages onto one 12x18 sheet
I'm trying to figure out how to format a 12 x 18 sheet with four individual 5.5 x 8.5 pages on it. Any ideas? The closest I can get is under page setup...selecting "postcards" and that will give me three pages per sheet, but I need four. Any ideas or thoughts are greatly appreciated. If your printer can be custom setup to 12 x 18 then you can divide it into four parts. Are you sure you aren't trying to setup a 11 x 17 landscaped sheet? In any event, setup your printer first. File, Page Setup, Advanced on the right, type 18 width, 12 height or 17 width, 11 ...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

Restoring another PST?
Hi everyone, I use the MS Outlook Backup tool, and it backs up daily. Early this week I imported the backed-up PST file ( over 1GB ) into my new PC and after a bit of fiddling to show the folders it did open up all the ones I wanted, yet seemed to be an old backup - maybe 12 weeks out of date. I had to use the machine so kept it like this, and have of course received many mails since then. I see another copy of the backup[ PST file and the date says it was created on Feb 19th, which would be right. My ( evantual! ) question is: how do I, in idiot-proof steps, go about restoring this other...

how to deal with duplicate data rows
I need to interface with a program that generates a .csv file with row of data. It generates 2 or more rows for each instance uniquely identified by the docket/page combination Example: last first bank trustee docket page smith john ny bank (blank field) 12235 8907 (blank field) (blank field) cal western 12235 8907 smith janet ny bank (blank field) 12235 8907 I need to consolidate all three lines into one row by concating field that contain different data (first name column) and columns with blan spaces to look like the ...

Compare Sheets #2
I'm going insane I'm trying to compare two sheets using Walkenbach's approach but for some reason when I use it...certain values don't show differences. Example: Sheet 1 (Range Name is Grid) has 18000 on Cell A1 Sheet 2 (Range Name is OldGrid) has 18000 on Cell A2 So if I change Cell A1 on Sheet 1 to 1 it should show me change but no...it just does nothing. However if I type 133...it shows change. Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=COUNTIF(OldGrid,A1)=0" Selection.FormatConditions(1).Interior.ColorIndex = 40 Help really appreciate...

Why do sheet tabs keep disappearing? Box in Tools is checked.
When I open a new or existing Excel file, the sheet tabs at the bottom may or may not show up. I have repeatedly gone to Tools, Options, View and made sure the sheet tabs box is checked, but still no tabs. How do I get them back? I can't get from one page of a workbook to another. This is in Offfice 2003. Hi Arlie, Try Tools/Options/General In the Sheets in new workbook box, check and see how many sheets are set to appear when opening a new workbook. Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://w...

more than 65, 500 rows
Is there a product that allows more than 65k rows? ...

Formula involving different sheets
Hi Using Excel 2003. In sheet 1, cell A1: =200/1200 In sheet 2, cell B2: =300/1200 In sheet 3 in a cell, I want to do =200/1200 + 300/1200 by cell reference. How do I do so? Thanks. Your formula would be: =sheet1!a1+sheet2!b2 The best way to create these formulae is to get Excel to do it. 1: Type = into your cell 2: Navigate to your sheet1!a1 cell. You will notice in the formula bar that Excel is adding its address to your formula 3: Type + 4: Navigate to your sheet2!b2 cell. 5: Hit enter Once you learn this, you will never type a cell address again. Regar...