Code to delete rows not working

Hi, I hope someone can help with this.

This is probably really obvious, but I've got the following code held
on Sheet 2 of a 3 sheet document. The cells concerned are filled by
data from Sheet 1: =Sheet1!A6, for example. I can't see why it wont
work.

Basically, I want those A-column cells on Sheet 2 which don't hold a
value (their corresponding sheet on Sheet 1 is empty) to hide their
row. I've tried ascribing a value in the 'If cell.Value=' section.
(The cells are formatted for dates. A nil return is giving me
00-Jan-00; I've tried using If cell.Value<01-Jan-00, and 01-Jan-2003)

The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
'Hide empty rows
Set rng = Range("A6:A21" & Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In Range
   If cell.Value = "" Then cell.EntireRow.Hidden = True
Next cell

End Sub

Any answers would be greatly appreciated.

Steve
0
8/21/2004 4:37:12 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
668 Views

Similar Articles

[PageSpeed] 52

Hi Steve!

Ask yourself a few questions:

What will trigger the action of hiding rows? Just any change in th
active sheet?

Which sheet is the active sheet? Does Excel know from your code?

When you are working with 2 sheets "on the go" are you being meticulou
in telling Excel which one to treat at that time?

Why define rng and then call it Range?

Come back if you need to ;)

Al

--
Message posted from http://www.ExcelForum.com

0
8/21/2004 5:10:01 PM
Private Sub Worksheet_Change(ByVal Target As Range)
'Hide empty rows
Dim rng as Range, cell as Range
Set rng = Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row)
rng.entireRow.Hidden = False
For Each cell In Range
   If cell.Value2 = 0 Then cell.EntireRow.Hidden = True
Next cell
End Sub

-- 
Regards,
Tom Ogilvy


"CrankyLemming" <crankylemming@aol.com> wrote in message
news:fc55430.0408210837.5b5c394f@posting.google.com...
> Hi, I hope someone can help with this.
>
> This is probably really obvious, but I've got the following code held
> on Sheet 2 of a 3 sheet document. The cells concerned are filled by
> data from Sheet 1: =Sheet1!A6, for example. I can't see why it wont
> work.
>
> Basically, I want those A-column cells on Sheet 2 which don't hold a
> value (their corresponding sheet on Sheet 1 is empty) to hide their
> row. I've tried ascribing a value in the 'If cell.Value=' section.
> (The cells are formatted for dates. A nil return is giving me
> 00-Jan-00; I've tried using If cell.Value<01-Jan-00, and 01-Jan-2003)
>
> The code is:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> 'Hide empty rows
> Set rng = Range("A6:A21" & Range("A" & Rows.Count).End(xlUp).Row)
> For Each cell In Range
>    If cell.Value = "" Then cell.EntireRow.Hidden = True
> Next cell
>
> End Sub
>
> Any answers would be greatly appreciated.
>
> Steve


0
twogilvy (1078)
8/21/2004 5:15:04 PM
Hi Tom!

For Each cell In Range

might better read 

For each cell in rng ;)

Al

--
Message posted from http://www.ExcelForum.com

0
8/21/2004 9:30:50 PM
Tom Ogilvy wrote:

> Private Sub Worksheet_Change(ByVal Target As Range)
> 'Hide empty rows
> Dim rng as Range, cell as Range
> Set rng = Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row)
> rng.entireRow.Hidden = False
> For Each cell In Range
>    If cell.Value2 = 0 Then cell.EntireRow.Hidden = True
> Next cell
> End Sub

Thanks Tom.

I made a couple of slight changes: ...cell in rng, and If
cell.Value=0, but also amended this to a Worksheet_Calculate() sub.

For my information, is there a short answer to  - what's the
difference between the _Change and _Calculate here?

Thanks

Steve
0
8/22/2004 6:44:48 PM
Calculate fires whenever a cell in the worksheet is calculated. Change 
fires whenever a manual entry (or change to an external link) is made.


In article <fc55430.0408221044.4df7c2fc@posting.google.com>,
 crankylemming@aol.com (CrankyLemming) wrote:
> 
> For my information, is there a short answer to  - what's the
> difference between the _Change and _Calculate here?
0
jemcgimpsey (6723)
8/22/2004 8:21:37 PM
Cell.Value2 is correct.  Value2 returns the number stored in the cell, not a
date.

Range("B9").Value = "03/10/2004"
? range("B9").Value
3/10/04
? range("b9").Value2
 38056

>For Each cell In Range
Range should be rng
(I inherited that from your code - didn't pick it up to correct it)

Calculate fires whenever any action causes the sheet to calculate

Change fires when you edit a cell.

if you want to restrict it to cells that are changed in A6 to the last row

Private Sub Worksheet_Change(ByVal Target As Range)
 'Hide empty rows
 Dim rng as Range, cell as Range
 If target.count >1 then exit sub
 Set rng = Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row)
if not intersect(target,rng) is nothing then
 rng.entireRow.Hidden = False
 For Each cell In Range
    If cell.Value2 = 0 Then cell.EntireRow.Hidden = True
 Next cell
End if
End Sub

-- 
Regards,
Tom Ogilvy


"CrankyLemming" <crankylemming@aol.com> wrote in message
news:fc55430.0408221044.4df7c2fc@posting.google.com...
> Tom Ogilvy wrote:
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > 'Hide empty rows
> > Dim rng as Range, cell as Range
> > Set rng = Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row)
> > rng.entireRow.Hidden = False
> > For Each cell In Range
> >    If cell.Value2 = 0 Then cell.EntireRow.Hidden = True
> > Next cell
> > End Sub
>
> Thanks Tom.
>
> I made a couple of slight changes: ...cell in rng, and If
> cell.Value=0, but also amended this to a Worksheet_Calculate() sub.
>
> For my information, is there a short answer to  - what's the
> difference between the _Change and _Calculate here?
>
> Thanks
>
> Steve


0
twogilvy (1078)
8/23/2004 4:10:58 AM
Alf,
Talk to Steve, that was his code. I guess I didn't pick up all the mistakes.

-- 
Regards,
Tom Ogilvy

"AlfD >" <<AlfD.1bcy5c@excelforum-nospam.com> wrote in message
news:AlfD.1bcy5c@excelforum-nospam.com...
> Hi Tom!
>
> For Each cell In Range
>
> might better read
>
> For each cell in rng ;)
>
> Alf
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
twogilvy (1078)
8/23/2004 4:12:04 AM
Reply:

Similar Artilces:

Integrate Paycodes, benefit codes and deduction codes
Has anyone used integration manager to update new pay rates, deduction amounts and benefit amounts for employees? At the beginning of each year, our company gives pay increases and we need to update the pay codes, deduction codes and benefit codes for 40 employees, which we get the information from a spreadsheet. I thought that maybe I could use integration manager to update the pay, benefit and deduction codes instead of going into each employee's card, which is time consuming. Thanks, Laura Integration Manager will allow you to do this. Use the Payroll Master Destination. one ...

Can't delete file
I just did a system recovery on a Gateway with XP Pro from the restore partition on the hard drive. One of the restore options was to save the old windows files in a folder called Backup (date). So I did that. After restoring the OS I copied all the files I needed back into the new install from the backup folder. Then I wanted to delete the backup folder. Everything deleted OK except... C:\Backup\Windows\System32\Macromedia\Flash\flash8a.ocx No way in hell can I get it deleted. "access denied". I booted into Safe Mode where I could get to the security options and no mat...

Can't delete Money investment; investment gets recreated after every download
Hi, I'm running into an annoying issue with Money 2006: A while ago I accidentally set up two investments for MSFT: one with an associated stock symbol, and the other obviously with no symbol. I would like to merge these two investments. I tried doing that by remapping all of the 'wrong' investment transactions to the right investment (the one with the stock symbol), and then deleting the wrong investment. However, every time I go to download account updates from Fidelity, I get a prompt saying "you've got xxx stock of Microsoft Corp. (the wrong type)". If I le...

Is there a way to delete multiple queries at the same time?
I have a database that has 2008 and 2009 records. I want to delete the 2008 queries. Is there a way to delete multiple queries at the same time? It takes so long to delete them 1 by 1. Also, at the end of each year I have to copy the database and change the name to the new year, now is 2010. Then I have to go into that database and reconfigure all the queries to use 2010 instead of 2009, for example. After I reconfigure the new years queries, having them look towards the 2010 database instead of 2009, I have to delete the old year's records, tables and queries. Is th...

zip codes don't merge #2
I am trying to mail merge w/ Word 2000 the names and addresses in my worksheet. When I get to the part to choose the format for the mailing labels, I choose F1, F2, etc. to F6 (which is the zip code column). A few do get there, but the vast majority stop at the state, leaving off the entire zip code. I have gone to menu/format and selected text in the number tab. I have gone to format/cells and chosen special/zip code in the number tab. I've read Excel for Dummies. Please help me. TIA bb ...

Deleted Mails came back into mailbox
Hi, i have an outlook xp user. Their mailbox was very large and i asked them to clean up their deleted and sent items folders. they done this for me, but.. the day after they done this, all mails they deleted SAME BACK!!!! ie Monday - deleted mails Tuesday - mails back Tuesday - deleted mails Wednesday - deleted mails!! This is sooooo strange, please help ...

How to delete a project from the list?
When entering a transaction into a bank account, you can choose a project from a list. How can I remove an unneeded project from that list? In microsoft.public.money, Paul Pedersen wrote: >When entering a transaction into a bank account, you can choose a project >from a list. > >How can I remove an unneeded project from that list? > In Money 2006, go to Banking->AccountTools->Categories&Payees and click your project in the left column. Click DeleteClassificaton. Sorry, I didn't mention that I'm still using 2003... but with your help, I found it. Tha...

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

concatenating two vendor codes
My company recently changed the vendor codes and now I end up with two separate sets of data for each vendor. Obviously I could just leave the vendor code field out. I don't want to do that so what I need to do is concatenate the two codes. Example Vendor Code Vendor Name 123 Joe's Supplies ABC Joe's Supplies what I need is this Vendor Code Vendor Name 123/ABC Joe's Supplies any ideas? Rather than create a new record for each vendor, add another field to the table -- call it NewVendorCode -- and put th...

Turn off save work reminder
How can I stop being reminded to save my work in Publisher 2000? Thanks in advance, Josh Options, User Assistance tab, clear "Remind to save publication." -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "josh page" <josqb@cinci.rr.com> wrote in message news:46b0a69f$0$8926$4c368faf@roadrunner.com... > How can I stop being reminded to save my work in Publisher 2000? > > Thanks in advance, > Josh > ...

making a program rotate and delete
I am so stuck. i am trying to make a wresting program in excel. Th top row is weight the first colum is the names of each wrestlers coach Each coach gets to pick up to 5 wrestlers that they do NOT want t fight theirs . and the wrestlers names get putunder their weigh class. B]I need the program to rotate each weight class so it wil randomly match up wrestlers but NOT the ones that don't want to fight. Am i explaing this well, (i doubt it) [/B] thanks -- Message posted from http://www.ExcelForum.com I still can't figure it out anyone have any ideas -- Message posted from htt...

Windows live messenger has stopped working
when I boot my PC and start Windows 7 WLM during the startup phase stops working before it has fully started. If however i start it after the boot up process is complete it works fine. I am using Window 7 64 Home Prem... and am running a new ATI 5800 series card, have 4g of ram etc..... Any ideas? I have read through the posts on this board and i do not see my problem though it is likely related to the other ones i have seen. Greetings, It might be a bit hard to isolate what's causing this without disabling other things starting at startup, as it's probably just a...

OL2002 : "header only" option works only once !!!!!
Hi, I have an IMAP account and OL2002 as client. I have check for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit, download item description only). It works only the first time i start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Can MS EXCEL remove duplicates and separate by color coded items ?
Can MS EXCEL remove duplicates and separate by color coded items ? I am NOT technical and have just started using MS EXCEL. Can anyone PLEASE HELP me: 1. How can I automatically remove duplicates using EXCEL ? ie the same info input more than once on different lines. Can EXCEL do this ? 2, I have color coded the text in the certain lines in terms of priority. can Excel rearrange the data by color ? If yes, How do I do it ? I think I have EXCEL '97 Thanks for your help in advance. Hi 1. You can extract the unique items to a new list using menu Data > Filter > Advanced fil...

16 bit code
Hello, I have a 16 bit code to maintain. (It is too complex to copile it to 32 bit) so all we are doing is support. Now they want to add a small feature, where I need to copy long file names in the project. I know we can not do long file name copies in vc++ (1.52). So I wrote a program in VC++(6.0) which does directory copy. My questions are 1. How can I call a 32 bit exe (I guess WinExec should work)? 2. How can I make the 16 bit to wait till the 32 bit code completes? 3. Is there any other better way to do this? (other than converting to 32 bit) Thanks. I actually do this in an old 16-bi...

How? compare two worksheets and identify rows of data that appear on both.
Ok, Suppose I have 2 worksheets. Sheet 1 has 1000 lines of data spanning xNumber of columns. Sheet 2 has 500 lines of data, also spanning the same number of columns. An unknown number of identical lines of data appear on both sheets. How can I get to a point where I can build one worksheet that is a composite of both sheets - such that no identical line of data appears twice on the final sheet? Thanks in advance, Simon. You could do this several ways. It really depends on your data integrity and what sort of data it is. -- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row...

Auto-Delete Task Items
I would like to delete a task when it's complete. I don't want go into each task to delete it. I want it click once and it's gone. I do not want to keep complete items taking up un-necessary space. How do I get Outlook to delete, completely delete ALL finished tasks automatically? I know there's no setting for this, but I was wondering if there were steps to create a rule to accomplish this. I use Outlook 2007 and when I click on Tools, Rules ..., I get a wizard and no way to start with a totally blank rule set so I can select tasks and not messages. Any help for ...

folder deleted while trying to delete one file in Outlook Express
I wanted to delete one file but instead it deleted the entire folder. It was not sent to the deleted folder nor to the recycle bin. Why did this happen? More important is there anyway that I can retrieve the files in the folder. Thanks in advance. Gil Hoellerich It happened cause you pressed delete and then confirmed it by pressing yes. I don't believe there is any way to recover it other than by restoring from backup. "G Hoellerich" <ghoellerich@NC.RR.com> wrote in message news:01de01c35a02$42738fd0$a101280a@phx.gbl... > I wanted to delete one file but instead it d...

area codes is auto filling my own 9 digit ph# vs just area code
When I enter a phone number for a contact it autofills with my personal area code and phone number instead of just the area code. How to I change this to just autofil the area code? I am using Outlook 2007 on an ACER laptop. Make sure you have your area code entered correctly in "Dialing Properties". "Computer Dummy" wrote: > When I enter a phone number for a contact it autofills with my personal area > code and phone number instead of just the area code. How to I change this to > just autofil the area code? I am using Outlook 2007 on an ACER ...

VBA Code for Pasting Sheets
I would like a spreadhseet that pastes the contents of one sheet into another sheet. I like like to do this for 7 different sheets For example: I would like paste the contents form sheet titled "sheet1" into a sheet titled "data1". Continue to process for pasting "sheet2" into "data2" and "sheet3" into "data3" all way until "sheet7" and "data7". thanks, Curt Subject: Automated Copy Paste Subject: Copy/Paste Import/Export Data VBA Code On Apr 27, 10:49=A0am, Curt <C...@discussions.mi...

MC/VISA joint code
Isn't there any way to combine the blocks (4* & 5*) for MC/VISA credit cards? It's a pain to have them separate because my bank posts them together. It is the same company afterall. It would save me a lot of time adding separate entries together to reconcile the statement! I do not know of a way to do what you are asking, but I have found that using the # symbol as follows works better than using the * symbol: 4############### 5############### If you use the * symbol it is a wild card with no determined length. The # symbol requires that the characters after the first d...

+ on top of columns & next to rows
I have a spreadsheet that has + on top of the columns and next to the rows. when you click on the + it hides or unhides the row/columns. there are also numbers at the very top left hand corner that when clicked on hides or unhided all of the corresponding rows/columns. I would like to do this to other spreadsheets. Can someone please let me know how this is done? Thanks in advance. Look up "Outline" in the HELP files. It's the same format as when you use subtotals. -- HTH, RD ============================================== Please keep all correspondence within the G...

Money 2004 - Experian Offer
I just loaded Money 2004 Deluxe and converted my 2003 files with no problem. I decided to give the free year of credit monitoring from Experian a try but after filling everything out I get an error that the provided code has expired. I neither had nor provided any code for this offer, I only used the link from Money 2004. Now Experian was more than willing to set up a $79 account if I wanted to proceed. Any solution out there? I have the same problem... I am looking for a FREE way to contact Microsoft about this problem. >-----Original Message----- >I just loaded Money 2004 ...

Analytical Accounting
Hi, One of my Client's is considering to go down AA path with approx 300,000 AA dimension codes. I would like to hear whether such data set is manageable from Query wizard etc. appreciate your thoughts. Good Morning PR, I found that the issue isn't the number of AA Trx Dim Codes it is the size of the AAG30000 and AAG40000 tables the more entries the longer it takes to product the report. I had one client that started having a cached SSRS report with AA information as a management level report and a different SSRS report with a more restrictive dataset for reporting ...

Outbox continues to send messages. Can't delete msg in Outbox
Hi. I am having a problem with my Outbox. I have 4 messages in the Outbox that resend each time I log in. I can't delete the messages from the Outbox. I can't move the messages out of the Outbox. I tried working offline to see if I could delete them to no avail. I tried right- click delete to no avail. I even tried shift-right-click to no avail. When I login the message send but stay in the Outbox as if they didn't send. They do appear in my Deleted Items box and I can delete/move them from that folder but not the Outbox folder. I am not sure if a reload of Outlo...