Code to delete rows excluding Header if not = to

Good morning

I have the following code to delete all rows that do not contain the 
following two variables, in column P, "cw" and "ea".

Set ws = ActiveSheet
     
For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
    If ws.Cells(p, 16) <> "cw" Then
    If ws.Cells(p, 16) <> "ea" Then
        ws.Rows(p).Delete
    End If
    End If
Next

It works very well apart from the fact that it deletes the Header row.  
Please can someone assist me in modifying this code so it does not delete Row 
1 (the header row).

Thanks for any help in advance, I really appreciate it.

0
Utf
3/22/2010 9:26:01 AM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
1351 Views

Similar Articles

[PageSpeed] 9

Try changing your loop control to :

For p = ws.Range("P65536").End(xlUp).Row To 2 Step -1

Regards

Kevin


"PVANS" <PVANS@discussions.microsoft.com> wrote in message 
news:692E1DCE-3A31-4698-850E-6588ABB53AF2@microsoft.com...
> Good morning
>
> I have the following code to delete all rows that do not contain the
> following two variables, in column P, "cw" and "ea".
>
> Set ws = ActiveSheet
>
> For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
>    If ws.Cells(p, 16) <> "cw" Then
>    If ws.Cells(p, 16) <> "ea" Then
>        ws.Rows(p).Delete
>    End If
>    End If
> Next
>
> It works very well apart from the fact that it deletes the Header row.
> Please can someone assist me in modifying this code so it does not delete 
> Row
> 1 (the header row).
>
> Thanks for any help in advance, I really appreciate it.
> 


0
kc
3/22/2010 10:21:46 AM
aha! managed to fix it, I simply changed:
For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1

to:
For p = ws.Range("P65536").End(xlUp).Row To 2 Step -1

Have tested that it still works correctly, by placing a row in row to that 
is not equal to "cw" and it was correctly deleted.

Thanks to any and everyone who took a look at this for me

"PVANS" wrote:

> Good morning
> 
> I have the following code to delete all rows that do not contain the 
> following two variables, in column P, "cw" and "ea".
> 
> Set ws = ActiveSheet
>      
> For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
>     If ws.Cells(p, 16) <> "cw" Then
>     If ws.Cells(p, 16) <> "ea" Then
>         ws.Rows(p).Delete
>     End If
>     End If
> Next
> 
> It works very well apart from the fact that it deletes the Header row.  
> Please can someone assist me in modifying this code so it does not delete Row 
> 1 (the header row).
> 
> Thanks for any help in advance, I really appreciate it.
> 
0
Utf
3/22/2010 10:42:01 AM
Hi Kevin,

Thanks for the reply - I had literally just tried that and it works 
perfectly.  Thanks for the help.  I do appreciate it

Have a good day

Paul

"kc-mass" wrote:

> Try changing your loop control to :
> 
> For p = ws.Range("P65536").End(xlUp).Row To 2 Step -1
> 
> Regards
> 
> Kevin
> 
> 
> "PVANS" <PVANS@discussions.microsoft.com> wrote in message 
> news:692E1DCE-3A31-4698-850E-6588ABB53AF2@microsoft.com...
> > Good morning
> >
> > I have the following code to delete all rows that do not contain the
> > following two variables, in column P, "cw" and "ea".
> >
> > Set ws = ActiveSheet
> >
> > For p = ws.Range("P65536").End(xlUp).Row To 1 Step -1
> >    If ws.Cells(p, 16) <> "cw" Then
> >    If ws.Cells(p, 16) <> "ea" Then
> >        ws.Rows(p).Delete
> >    End If
> >    End If
> > Next
> >
> > It works very well apart from the fact that it deletes the Header row.
> > Please can someone assist me in modifying this code so it does not delete 
> > Row
> > 1 (the header row).
> >
> > Thanks for any help in advance, I really appreciate it.
> > 
> 
> 
> .
> 
0
Utf
3/22/2010 11:00:01 AM
Reply:

Similar Artilces:

Deleted batch
Hi guys: Is there any way I can determine who deleted a batch in GP? I have the batch number and I was looking in SY00500 but it appears that a deleted batch is removed from that table. TIA. Norman -- Norman This was posted in error please ignore. -- Norman "Norman Daley" wrote: > Hi guys: > > Is there any way I can determine who deleted a batch in GP? > > I have the batch number and I was looking in SY00500 but it appears that a > deleted batch is removed from that table. > > TIA. > > Norman > > ...

Moving Rows to Columns
I have a text file that I am importing that has information on multiple rows (each entry has three rows of data). I would move each row of data into a column. Anyone have any ideas/macros on how to do this? Dear Paul Select the row of data, Copy it, move to the cell of your choice (I would recommend on a new worksheet).Edit/Paste Special/Tick the transpose box. Hope this helps Paul Falla >-----Original Message----- >I have a text file that I am importing that has >information on multiple rows (each entry has three rows of >data). I would move each row of data into a co...

Exchange 2003 Server
I accidently deleted a Exchange server from a System Policy and it removed the server from the organization. How do I get that back????? Do I need to reinstall Exchange? You could do an authoritative restore of Active Directory otherwise you will need to do another install for the server. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply to this newsgroup. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright....

Deleted e-mails
I have outlook 2000. Is their any way to retrieve deleted e-mails (deleted from the deleted items file)? If you connect to an Exchange server configured to allow this, yes. If not, no, not unless you had a backup of your PST file...sorry. Senait wrote: > I have outlook 2000. Is their any way to retrieve deleted > e-mails (deleted from the deleted items file)? ...

Can I delete my Entourage 2004 Identities once I've upgraded to 20
I just (finally) upgraded to Office 2008, but I need to keep Excel 2004, so I didn't remove Office 2004. Now I've got two identical (and huge) Identity folders, and I was wondering if I can delete the 2004 Identity if I'm going to use Entourage 2008? Thanks! On 4/19/10 9:26 AM, Jg wrote: > I just (finally) upgraded to Office 2008, but I need to keep Excel 2004, so I > didn't remove Office 2004. Now I've got two identical (and huge) Identity > folders, and I was wondering if I can delete the 2004 Identity if I'm going > to use Entourage 2008?...

How do I prevent users to delete a worksheet, but allow to edit?
How can I allow other users to update an workbook, but not to delete any tabs or existing information? Take a look at protect worksheet and protect workbook these both can be found in tools>protection. Use help to guide you the rest of the way. James "nb91386@yahoo.com" wrote: > How can I allow other users to update an workbook, but not to delete any tabs > or existing information? Protect the workbook. Tools|protection|Protect workbook Just leave Structure checked. (Users won't be able to rename or change the order of the worksheets, either.) But be aware that...

Moving The Cursor By Code
Access 2007 I want to be able to move the cursor using VBA code. I put the following in a module: Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINT) As Long Private Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long Public Function MoveMouseToTopLeftCorner(ByVal vlHwnd As Long) As Boolean Dim lReturn As Long Dim pnt As POINT Dim rec As RECT lReturn = GetWindowRect(vlHwnd...

Deleting info in a column
I am exporting info from MS Project and my dates come out formatted as such: 2/5/2007 8:00 I do not need to see the time, and i know there is a function (or something) that will run through the column and delete the time to just show 2/5/2007 ...... I can not remember how to do this. any help appreciated! If you just want to change the way the info is displayed, one way would be to format the column as a date. In article <1179770254.963135.161610@a26g2000pre.googlegroups.com>, JPD <jdescant@gmail.com> wrote: > I am exporting info from MS Project and my dates come out...

COM port, some problem in my code.
Hello, I want to use COM port. I have a cable that connect between port1 and port2. For the following attached program (several attached files), I don't understand what's wrong, because after clicking receive button, I don't get the string I send (by the send button : abc). What is wrong in my code ? Help, please. Thanks :) There was some problem to send an attachment file to this newsgroup. Where can I send the attachment file ? For the case here is the relevant code : I do run at the order respectively. OnOpen, OnSend, OnReceive, OnMyClose. m_com_port_1 and m_com_port_2...

Public Function only called for details in first grouped header
I have 2 values in the detail section of my report that call a public function in a common module. It works fine for the first "set" of detail records within the first grouped header, but all subsequent header groups display #Error -- essentially the function was never called. Is this expected functionality (from Access, anyway)? thanks in advance, Patrick You should be able to use a public function from almost any control source. Can you share anything about the function or its arguments or return values or whatever? -- Duane Hookom Microsoft Access MVP "pheron@gmail...

deleting specific emails from exchange server
Is it possible to delete specific emails from exchange server 2000? I have sceneraio where 20,000 have been generated for a single email address and I want to be able to delete these before delivery to the end user account (outlook 2003). Cheers in advance Ollie Riches Check your mail queues, order them by Sender, bulk select and then delete with no NDR. Oliver "Ollie Riches" <ollie_riches@hotmail.com> wrote in message news:OSIRe2iNHHA.2232@TK2MSFTNGP02.phx.gbl... > Is it possible to delete specific emails from exchange server 2000? > > I have scenerai...

Button to delete all records of a table
Hello, Can anyone help me how can we make it just to pressed the button, then all records in a table will be deleted/ purge out. Background: I want to distrtibute copy of my church database to all pastors of our church. In my membership database, there is a child table of Notes...in which a church clerck can put a note for the member. Before I give the copy of the database to pastors, I want to purge out all the content of the table of Notes, so that later I can collect it from them and inport their notes table to be combined with the central database which is the one a sc...

"headers"
Is there a way that a header row can be printed on each page of multipage document without manually inputing the header row at th start of each page. We may be adding more information to a page tha will cause the "page breaks" to change on each page. Thank you An -- aletoconstc ----------------------------------------------------------------------- aletoconstco's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3533 View this thread: http://www.excelforum.com/showthread.php?threadid=57289 Ann, file page set up, sheet, rows to repeat at top $1:$1, o...

Changing page size and retaining header and footer
I changed my page size to 7 X 10. Now Word 2003 will not let me change it back to 8.5 X 11 (standard page size) and the header and footer will not show unless I go to View and click on them. I have tried File--page set-up, etc. with no luck. It says that it is set at 8.5 X 11, but that is NOT what shows on the screen. HELP!! See http://www.gmayor.com/white_space.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://...

Bar Code Scanner Opos
We typically just plug in our barcode scanners via USB and let windows automatically install. It's easy and works. Is there any reason that I should actually be using the scanners in a serial mode with OPOS drivers installed? I just don't see the need or the benefit, but maybe I am missing something. Thanks .. S ...

Deleting GL accounts
We are receiving an error message when trying to delete some GL accounts - this account has a balance or YTD activity and cannot be deleted. The account has had no activity for several years and has been inactivated. We just processed our year end and a BBF of zero was brought forward. Do we need to Remove Account History before we can delete these accoutns? Yes, you need to delete history. To delete an account it must meet the following conditions - 1. No balance 2. No activity for an open period 3. No history amounts 4. Not part of an allocation account 5. Not part of an unpo...

How do I stop Excel from automatically resizing my row height?
I have a spreadsheet with approximately 3800 rows and about 40 columns of data that I'm constantly filtering and massaging data in. It's getting frustrating to have my rows all of a sudden jump to a height of about 1/4 of the screen when I shrink some column widths to fit more columns on the screen. (Freeze panes won't allow me to split the screen also.) How do I disable the autofit row height? ...

Can't insert Rows?
When I try and insert a row I get a prompt saying "To prevent possible loss of data, Excel cannot shift non-blank cells off the worksheet. Try to locate the last non-blank cell by pressing CTRL+END and delete or clear all cells in between the last cell and the end of your data. Then select cell A1 and save your workbook to reset the last cell used." Can anyone advise (a) Why this happens and (b) How to stop it happening as I've tried doing what the prompt says and can't seem to solve it...and I really need to insert a row, rather than cut & paste. Any help great...

DPM agent for exchange 2007 fails with 80070643 error code
Greetings! I'm completely frustrated. When I try to install DPM agent on exchange 2007 i allways receive the same error 80070643 regardless of firewall settings or dpm agent version. Please help! My config is: exchange 2007 SP1 - on w2k8 x64 AD - in w2k3 native mode DPM 2007 - on w2k3 r2 32bit Hi, We understand your frustration. We will look into it to get better informative error message. For this, please collect the logs from PS server shared at: "%SystemDrive%\Windows\Temp\MSDPM*.log. Mail these logs to: anuraga@microsoft[DOT]com Thanks Anur...

Mails are automaticaly deleted after being reading
Hello all, I've got a little problem with Outlook 2002. Wenn I get a new message in the receive box and I open it to read it, and wenn I click on the cross to close the email, the mail is automaticaly deleted from outlook without being moving in the deleted items folder. The problem is if I don't answer directly, the message is lost. I already checked the options but I can't found which one musts be changed to keep all the messages in the incomming box. I hoppe you will anderstand my problem and that someone will be able to help me Best regards, Tommy. Tommy <thierry...

Previously permitted folders will not delete
Using Outlook 2000, I have found that when User A has been given permission to access User B's folder(s), then User B removes the permissions for User A to access the folder(s) any longer, User A's Outlook setup will still show User B's folder(s) in their list of accessible folders when in option File => Open => "folder". Is there a way to delete the no longer accessible folder from the list? Thx. Check out http://support.microsoft.com/default.aspx?scid=kb;en-us;292024&Product=out -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professiona...

Lock Rows
Hi, I have a worksheet where the first 5 rows (A1:A5) hold a number of headings which I want to be locked. ie when the user fills the current screen with data and wishes to scroll down using the vertical scroll bar, the actual headings don't dissapear from the screen. Easy enough, but can't figure out how. Thanks Anthony Go into A6 and then go to window/freeze panes "Anthony" wrote: > Hi, > I have a worksheet where the first 5 rows (A1:A5) hold a number of headings > which I want to be locked. ie when the user fills the current screen with > data and wish...

missing headers from e-mail
i have an end user who is missing the header from her e- mail and i have been unable to correct the problem. ...

Unknown Message Header & Pop3 Error Message
Windows Live Mail: I will start with 2 problems out of many. I get messages with unknown in the header. What could be causing this? I have messages popping up, at what I have now set at 120 seconds, that my pop3 server has not responded. How do I fix this? Why does Microsoft think they more about program interfaces than the people that actually use the programs? I am sure people like WLM but I do not. I wonder how Microsoft could be made to realize some people would like an OE6 for Windows 7. I am running Windows 7 Professional. -- <Bill> Brought to you f...

Deleting worksheets
What is the best way to erase a worksheet, it shouldn't leave any trace - whats so ever - on the hard drive Hi see your other post -- Regards Frank Kabel Frankfurt, Germany anonymous@discussions.microsoft.com wrote: > What is the best way to erase a worksheet, it shouldn't > leave any trace - whats so ever - on the hard drive What ? >-----Original Message----- >Hi >see your other post > >-- >Regards >Frank Kabel >Frankfurt, Germany > > >anonymous@discussions.microsoft.com wrote: >> What is the best way to erase a worksheet, it should...