Tags: developed, empty, enabled, excel, forms, hide, insert, microsoft, msdn, print, program, rows, software, specified, user, vbe, working, worksheets

Hide empty rows in Excel worksheets

On Microsoft » Microsoft Excel

14,485 words with 5 Comments; publish: Thu, 22 May 2008 04:22:00 GMT; (30678.13, « »)

I'm working on a program that enabled the user to insert 10 information

and print them. I developed the forms using VBE.

I have specified all the information into their own cells in Excel

worksheets.

When the user insert Info1, the data will directly go to Row 2, Column

1 in Excel worksheet. When the user insert Info2, the data will

directly go to Row3, Column 1. Each info has their own formula.

What I wanted to do now is that, if let say, the user didn't insert

Info1, I want the program to automatically hide the row specified for

Info1, so that when the user print the form, the empty row is not on

the paper.

I tried to use this code, but it doesn't seems to work. Maybe because I

have specified the formula in the cells.

Code:

For i = 2 To 4

If Worksheets("PCR-Master").Cells(i, 1).Text <> "" Then

Worksheets("PCR-Master").Rows(i).EntireRow.Hidden = True

End If

Next i

Any ideas?

Thank in advanced

All Comments

Leave a comment...

  • 5 Comments
    • Your code seems to do the opposite of what you want.

      You have said that if the cell text is NOT blank, then hide the row. Try

      changing <> to = and see if that helps.

      --

      Ian

      --

      "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      news:1156491750.964399.210250.excel.todaysummary.com.i42g2000cwa.googlegroups.com...

      > I'm working on a program that enabled the user to insert 10 information

      > and print them. I developed the forms using VBE.

      > I have specified all the information into their own cells in Excel

      > worksheets.

      > When the user insert Info1, the data will directly go to Row 2, Column

      > 1 in Excel worksheet. When the user insert Info2, the data will

      > directly go to Row3, Column 1. Each info has their own formula.

      > What I wanted to do now is that, if let say, the user didn't insert

      > Info1, I want the program to automatically hide the row specified for

      > Info1, so that when the user print the form, the empty row is not on

      > the paper.

      > I tried to use this code, but it doesn't seems to work. Maybe because I

      > have specified the formula in the cells.

      > Code:

      > For i = 2 To 4

      > If Worksheets("PCR-Master").Cells(i, 1).Text <> "" Then

      > Worksheets("PCR-Master").Rows(i).EntireRow.Hidden = True

      > End If

      > Next i

      >

      > Any ideas?

      > Thank in advanced

      >

      #1; Thu, 22 May 2008 04:23:00 GMT
    • I'm actually used this sign ' = '. But, it doesn't worked out. However,

      when I used this sign '<>', it does worked out, when it hides all the

      cells that contain data.

      Why is it so? I'm so confuse......

      Ian wrote:

      > Your code seems to do the opposite of what you want.

      > You have said that if the cell text is NOT blank, then hide the row. Try

      > changing <> to = and see if that helps.

      > --

      > Ian

      > --

      > "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      > news:1156491750.964399.210250.excel.todaysummary.com.i42g2000cwa.googlegroups.com...

      > > I'm working on a program that enabled the user to insert 10 information

      > > and print them. I developed the forms using VBE.

      > > I have specified all the information into their own cells in Excel

      > > worksheets.

      > >

      > > When the user insert Info1, the data will directly go to Row 2, Column

      > > 1 in Excel worksheet. When the user insert Info2, the data will

      > > directly go to Row3, Column 1. Each info has their own formula.

      > >

      > > What I wanted to do now is that, if let say, the user didn't insert

      > > Info1, I want the program to automatically hide the row specified for

      > > Info1, so that when the user print the form, the empty row is not on

      > > the paper.

      > >

      > > I tried to use this code, but it doesn't seems to work. Maybe because I

      > > have specified the formula in the cells.

      > >

      > > Code:

      > > For i = 2 To 4

      > > If Worksheets("PCR-Master").Cells(i, 1).Text <> "" Then

      > > Worksheets("PCR-Master").Rows(i).EntireRow.Hidden = True

      > > End If

      > > Next i

      > >

      > >

      > >

      > > Any ideas?

      > > Thank in advanced

      > >

      #2; Thu, 22 May 2008 04:24:00 GMT
    • So when you use <> does it hide the rows that appear blank as well? If so,

      then the cells are not actually blank (could they contain a formula, or

      perhaps a space or other invisible character?)

      If it is hiding the cells with data, but not the blank ones, then I'm

      stumped. Substituting + for <> should completely reverse the formula.

      Try using Value instead of Text. No real reason why this should work, but it

      might be worth a try.

      I'm going to be away for a few days now, so if this doesn't work, perhaps

      someone lese can suggest a solution?

      --

      Ian

      --

      "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      news:1156733734.380757.276930.excel.todaysummary.com.m79g2000cwm.googlegroups.com...

      > I'm actually used this sign ' = '. But, it doesn't worked out. However,

      > when I used this sign '<>', it does worked out, when it hides all the

      > cells that contain data.

      > Why is it so? I'm so confuse......

      > Ian wrote:

      >> Your code seems to do the opposite of what you want.

      >> You have said that if the cell text is NOT blank, then hide the row. Try

      >> changing <> to = and see if that helps.

      >> --

      >> Ian

      >> --

      >> "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      >> news:1156491750.964399.210250.excel.todaysummary.com.i42g2000cwa.googlegroups.com...

      >> > I'm working on a program that enabled the user to insert 10 information

      >> > and print them. I developed the forms using VBE.

      >> > I have specified all the information into their own cells in Excel

      >> > worksheets.

      >> >

      >> > When the user insert Info1, the data will directly go to Row 2, Column

      >> > 1 in Excel worksheet. When the user insert Info2, the data will

      >> > directly go to Row3, Column 1. Each info has their own formula.

      >> >

      >> > What I wanted to do now is that, if let say, the user didn't insert

      >> > Info1, I want the program to automatically hide the row specified for

      >> > Info1, so that when the user print the form, the empty row is not on

      >> > the paper.

      >> >

      >> > I tried to use this code, but it doesn't seems to work. Maybe because I

      >> > have specified the formula in the cells.

      >> >

      >> > Code:

      >> > For i = 2 To 4

      >> > If Worksheets("PCR-Master").Cells(i, 1).Text <> "" Then

      >> > Worksheets("PCR-Master").Rows(i).EntireRow.Hidden = True

      >> > End If

      >> > Next i

      >> >

      >> >

      >> >

      >> > Any ideas?

      >> > Thank in advanced

      >> >

      >

      #3; Thu, 22 May 2008 04:25:00 GMT
    • Thank you sooo much, Ian. I don't know what else I can do to solve this

      matter.

      Ian wrote:

      > So when you use <> does it hide the rows that appear blank as well? If so,

      > then the cells are not actually blank (could they contain a formula, or

      > perhaps a space or other invisible character?)

      > If it is hiding the cells with data, but not the blank ones, then I'm

      > stumped. Substituting + for <> should completely reverse the formula.

      > Try using Value instead of Text. No real reason why this should work, but it

      > might be worth a try.

      > I'm going to be away for a few days now, so if this doesn't work, perhaps

      > someone lese can suggest a solution?

      > --

      > Ian

      > --

      > "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      > news:1156733734.380757.276930.excel.todaysummary.com.m79g2000cwm.googlegroups.com...

      > > I'm actually used this sign ' = '. But, it doesn't worked out. However,

      > > when I used this sign '<>', it does worked out, when it hides all the

      > > cells that contain data.

      > > Why is it so? I'm so confuse......

      > >

      > > Ian wrote:

      > >> Your code seems to do the opposite of what you want.

      > >>

      > >> You have said that if the cell text is NOT blank, then hide the row. Try

      > >> changing <> to = and see if that helps.

      > >>

      > >> --

      > >> Ian

      > >> --

      > >> "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      > >> news:1156491750.964399.210250.excel.todaysummary.com.i42g2000cwa.googlegroups.com...

      > >> > I'm working on a program that enabled the user to insert 10 information

      > >> > and print them. I developed the forms using VBE.

      > >> > I have specified all the information into their own cells in Excel

      > >> > worksheets.

      > >> >

      > >> > When the user insert Info1, the data will directly go to Row 2, Column

      > >> > 1 in Excel worksheet. When the user insert Info2, the data will

      > >> > directly go to Row3, Column 1. Each info has their own formula.

      > >> >

      > >> > What I wanted to do now is that, if let say, the user didn't insert

      > >> > Info1, I want the program to automatically hide the row specified for

      > >> > Info1, so that when the user print the form, the empty row is not on

      > >> > the paper.

      > >> >

      > >> > I tried to use this code, but it doesn't seems to work. Maybe because I

      > >> > have specified the formula in the cells.

      > >> >

      > >> > Code:

      > >> > For i = 2 To 4

      > >> > If Worksheets("PCR-Master").Cells(i, 1).Text <> "" Then

      > >> > Worksheets("PCR-Master").Rows(i).EntireRow.Hidden = True

      > >> > End If

      > >> > Next i

      > >> >

      > >> >

      > >> >

      > >> > Any ideas?

      > >> > Thank in advanced

      > >> >

      > >

      #4; Thu, 22 May 2008 04:26:00 GMT
    • I take it from your enthusiasm that it's sorted :-)

      Glad to help.

      --

      Ian

      --

      "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      news:1156813986.920171.245240.excel.todaysummary.com.m79g2000cwm.googlegroups.com...

      > Thank you sooo much, Ian. I don't know what else I can do to solve this

      > matter.

      >

      > Ian wrote:

      >> So when you use <> does it hide the rows that appear blank as well? If

      >> so,

      >> then the cells are not actually blank (could they contain a formula, or

      >> perhaps a space or other invisible character?)

      >> If it is hiding the cells with data, but not the blank ones, then I'm

      >> stumped. Substituting + for <> should completely reverse the formula.

      >> Try using Value instead of Text. No real reason why this should work, but

      >> it

      >> might be worth a try.

      >> I'm going to be away for a few days now, so if this doesn't work, perhaps

      >> someone lese can suggest a solution?

      >> --

      >> Ian

      >> --

      >> "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      >> news:1156733734.380757.276930.excel.todaysummary.com.m79g2000cwm.googlegroups.com...

      >> > I'm actually used this sign ' = '. But, it doesn't worked out. However,

      >> > when I used this sign '<>', it does worked out, when it hides all the

      >> > cells that contain data.

      >> > Why is it so? I'm so confuse......

      >> >

      >> > Ian wrote:

      >> >> Your code seems to do the opposite of what you want.

      >> >>

      >> >> You have said that if the cell text is NOT blank, then hide the row.

      >> >> Try

      >> >> changing <> to = and see if that helps.

      >> >>

      >> >> --

      >> >> Ian

      >> >> --

      >> >> "rixanna" <fildzana.excel.todaysummary.com.gmail.com> wrote in message

      >> >> news:1156491750.964399.210250.excel.todaysummary.com.i42g2000cwa.googlegroups.com...

      >> >> > I'm working on a program that enabled the user to insert 10

      >> >> > information

      >> >> > and print them. I developed the forms using VBE.

      >> >> > I have specified all the information into their own cells in Excel

      >> >> > worksheets.

      >> >> >

      >> >> > When the user insert Info1, the data will directly go to Row 2,

      >> >> > Column

      >> >> > 1 in Excel worksheet. When the user insert Info2, the data will

      >> >> > directly go to Row3, Column 1. Each info has their own formula.

      >> >> >

      >> >> > What I wanted to do now is that, if let say, the user didn't insert

      >> >> > Info1, I want the program to automatically hide the row specified

      >> >> > for

      >> >> > Info1, so that when the user print the form, the empty row is not on

      >> >> > the paper.

      >> >> >

      >> >> > I tried to use this code, but it doesn't seems to work. Maybe

      >> >> > because I

      >> >> > have specified the formula in the cells.

      >> >> >

      >> >> > Code:

      >> >> > For i = 2 To 4

      >> >> > If Worksheets("PCR-Master").Cells(i, 1).Text <> "" Then

      >> >> > Worksheets("PCR-Master").Rows(i).EntireRow.Hidden = True

      >> >> > End If

      >> >> > Next i

      >> >> >

      >> >> >

      >> >> >

      >> >> > Any ideas?

      >> >> > Thank in advanced

      >> >> >

      >> >

      >

      #5; Thu, 22 May 2008 04:27:00 GMT