How to return an array of values that are not zero/Null


A

apache007

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
Ad

Advertisements

J

JLatham

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub
 
A

apache007

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



JLatham said:
Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


apache007 said:
Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
J

JLatham

One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



apache007 said:
Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



JLatham said:
Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


apache007 said:
Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
A

apache007

Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


JLatham said:
One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



apache007 said:
Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



JLatham said:
Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
J

JLatham

I am NOT 'done' with the intro to VBA, but I think that perhaps there's
something of use in it even now. Either there is, or I've written almost 50
pages of useless gibberish! You're welcome to take a peek at it.

The most current version of the book may be downloaded, free of charge, from:

http://www.jlathamsite.com/LearningPage.htm

Look for the link to the .pdf document just below the heading "Introduction
to VBA Programming". I recommend right-clicking the link and choosing "Save
Target As" to get a copy of it onto your system.

My Excel-MVP-persona email address is in the book, and you're welcome to use
it to provide me any feedback for it. Just in case, the email addy is
(remove spaces)
Help From @ jlathamsite. com
If the links on the page I provided give you problems, let me know, I just
revised that LearningPage.htm today and haven't tested them all, but the link
to the .pdf file did work properly for me.

apache007 said:
Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


JLatham said:
One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



apache007 said:
Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
Ad

Advertisements

A

apache007

JLatham,

Thanks for the reference. I will get start on it.

I need a little more help on your coding.
Could you add the code on my example, so that it will display the same
result WITH EXCEPTION that SPECIFIC COLOUMN on sheet X contains text equal to
something ???

Thanks in advance.



JLatham said:
I am NOT 'done' with the intro to VBA, but I think that perhaps there's
something of use in it even now. Either there is, or I've written almost 50
pages of useless gibberish! You're welcome to take a peek at it.

The most current version of the book may be downloaded, free of charge, from:

http://www.jlathamsite.com/LearningPage.htm

Look for the link to the .pdf document just below the heading "Introduction
to VBA Programming". I recommend right-clicking the link and choosing "Save
Target As" to get a copy of it onto your system.

My Excel-MVP-persona email address is in the book, and you're welcome to use
it to provide me any feedback for it. Just in case, the email addy is
(remove spaces)
Help From @ jlathamsite. com
If the links on the page I provided give you problems, let me know, I just
revised that LearningPage.htm today and haven't tested them all, but the link
to the .pdf file did work properly for me.

apache007 said:
Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


JLatham said:
One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



:

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
J

JLatham

Could you phrase your request a little differently, I'm not understanding it
right now.

Do you mean you want things to work the way they do now except that if
something is in a particular column then don't do it, or do you mean that a
certain value must be in a particular column for it to do the move?

The change is going to be in this section:

For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next

and what we have to do is put another test in one way or another.
Keep in mind that our xSheetClassList is now made up of the used cells in
column A on sheetX. So when we use .OFFSET(row, COL) we modify the COL
parameter to identify the column to examine, where an offset of 0 = column A
itself, 1 would refer to column B, 2 = column C, etc.

Here's how it could look if we only do the move only when a particular value
is in column B (and it's for a text entry test)
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0,1) = "MUST BE THIS VALUE" Then
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
End If
Next

Here's how it could look if we only do the move only when a particular value
is NOT in column B (and it's for a numeric entry test)
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0,1) <> 3.14159 Then
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
End If
Next

Hope one of those helps you get it done they way it needs to be done.

apache007 said:
JLatham,

Thanks for the reference. I will get start on it.

I need a little more help on your coding.
Could you add the code on my example, so that it will display the same
result WITH EXCEPTION that SPECIFIC COLOUMN on sheet X contains text equal to
something ???

Thanks in advance.



JLatham said:
I am NOT 'done' with the intro to VBA, but I think that perhaps there's
something of use in it even now. Either there is, or I've written almost 50
pages of useless gibberish! You're welcome to take a peek at it.

The most current version of the book may be downloaded, free of charge, from:

http://www.jlathamsite.com/LearningPage.htm

Look for the link to the .pdf document just below the heading "Introduction
to VBA Programming". I recommend right-clicking the link and choosing "Save
Target As" to get a copy of it onto your system.

My Excel-MVP-persona email address is in the book, and you're welcome to use
it to provide me any feedback for it. Just in case, the email addy is
(remove spaces)
Help From @ jlathamsite. com
If the links on the page I provided give you problems, let me know, I just
revised that LearningPage.htm today and haven't tested them all, but the link
to the .pdf file did work properly for me.

apache007 said:
Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


:

One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



:

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
A

apache007

JLatham,

Is there an easier way to contact you? Do you have MSN/Yahoo msgr??



JLatham said:
Could you phrase your request a little differently, I'm not understanding it
right now.

Do you mean you want things to work the way they do now except that if
something is in a particular column then don't do it, or do you mean that a
certain value must be in a particular column for it to do the move?

The change is going to be in this section:

For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next

and what we have to do is put another test in one way or another.
Keep in mind that our xSheetClassList is now made up of the used cells in
column A on sheetX. So when we use .OFFSET(row, COL) we modify the COL
parameter to identify the column to examine, where an offset of 0 = column A
itself, 1 would refer to column B, 2 = column C, etc.

Here's how it could look if we only do the move only when a particular value
is in column B (and it's for a text entry test)
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0,1) = "MUST BE THIS VALUE" Then
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
End If
Next

Here's how it could look if we only do the move only when a particular value
is NOT in column B (and it's for a numeric entry test)
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0,1) <> 3.14159 Then
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
End If
Next

Hope one of those helps you get it done they way it needs to be done.

apache007 said:
JLatham,

Thanks for the reference. I will get start on it.

I need a little more help on your coding.
Could you add the code on my example, so that it will display the same
result WITH EXCEPTION that SPECIFIC COLOUMN on sheet X contains text equal to
something ???

Thanks in advance.



JLatham said:
I am NOT 'done' with the intro to VBA, but I think that perhaps there's
something of use in it even now. Either there is, or I've written almost 50
pages of useless gibberish! You're welcome to take a peek at it.

The most current version of the book may be downloaded, free of charge, from:

http://www.jlathamsite.com/LearningPage.htm

Look for the link to the .pdf document just below the heading "Introduction
to VBA Programming". I recommend right-clicking the link and choosing "Save
Target As" to get a copy of it onto your system.

My Excel-MVP-persona email address is in the book, and you're welcome to use
it to provide me any feedback for it. Just in case, the email addy is
(remove spaces)
Help From @ jlathamsite. com
If the links on the page I provided give you problems, let me know, I just
revised that LearningPage.htm today and haven't tested them all, but the link
to the .pdf file did work properly for me.

:

Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


:

One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



:

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 
Ad

Advertisements

J

JLatham

Best way is probably via email - remove spaces:
Help From @ jlathamsite. com
will get it to me. Just be sure to remind me of your apache007 id here so
I'll have a clue as to what the email is about.



JLatham said:
I am NOT 'done' with the intro to VBA, but I think that perhaps there's
something of use in it even now. Either there is, or I've written almost 50
pages of useless gibberish! You're welcome to take a peek at it.

The most current version of the book may be downloaded, free of charge, from:

http://www.jlathamsite.com/LearningPage.htm

Look for the link to the .pdf document just below the heading "Introduction
to VBA Programming". I recommend right-clicking the link and choosing "Save
Target As" to get a copy of it onto your system.

My Excel-MVP-persona email address is in the book, and you're welcome to use
it to provide me any feedback for it. Just in case, the email addy is
(remove spaces)
Help From @ jlathamsite. com
If the links on the page I provided give you problems, let me know, I just
revised that LearningPage.htm today and haven't tested them all, but the link
to the .pdf file did work properly for me.

apache007 said:
Thanks, Jlatham. I will start digging those sites on my free time.
If you are done with Intro to Excel VBA, that will help out a lot.
Please let me know if that ever published.


JLatham said:
One of the people in here that's a lot sharper than I am with Excel worksheet
functions might be able to come up with formulas for the second sheet to
update from the first, but I'm not that person.

One problem is that your second sheet has to know, somehow, how many entries
are on the first sheet - about the only way to do that with functions is to
fill lots of empty rows with the formulas in anticipation of future entries
on the source sheet. Then the formulas have to 'close ranks' - i.e., not
pick up entries except when there is a value to be brought over without
skipping rows.

Worksheet functions can't add other functions to a sheet, and they cannot do
things like cause a sheet to be filtered or hide/display rows/columns. So
for me, the easy path was to code up the solution - and since I'm a coder by
nature, that's the route I took.

Learning VBA
there are a number of site around the net to help.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.the-excel-advisor.com/excel-macros-vba-tutorial.html
http://class.et.byu.edu/ce270/vbaexcel_primer/intro.htm
http://www.exceltip.com/excel_links.html
there are other sites that provide usefull information about specific issues.
http://www.contextures.com/
http://www.cpearson.com/
http://www.j-walk.com/
http://www.mcgimpsey.com/
http://www.rondebruin.nl/
http://www.mrexcel.com
http://www.mvps.org/

I've had a project in progress for some time to write an "intro to Excel VBA
programming" and while I've made good progress with it, it keeps getting
pushed to the back burner and so I haven't got it to a point I feel
comfortable sticking it up on the internet as another free source of learning
(that would hopefully actually be useful also).



:

Jlatham,

Thanks for the advice. It works perfectly.

Boy...I do not expect I will need to do it through programming. Is there a
way doing that using just on Excel's feature???

And is there any good website to brush up my programming skill on VB
specializing on EXCEL???



:

Use the code below. It needs to go into the code module for your "Sheet Z".
To get it there:
Open the workbook, select Sheet Z and right-click on its name tab. Select
the [View Code] option from the list that appears.
Copy the code below and paste it into the module that appeared.
Modify any of the Const values that need to be modified. Close the VB Editor.

After that, each time you select Sheet Z, the list on it will be updated
from the list on Sheet X, so it will always be current based on the contents
of Sheet X.

There's also a line of code that is currently commented out that you can
turn into an active statement once you are sure things are working properly.
When you do that, it will update SheetZ much faster.

Here's the code, hope it helps:

Private Sub Worksheet_Activate()
'auto update from SheetX
'
'change these constants to
'match worksheet names and
'columns/rows used
'
Const sheetXName = "SheetX"
Const xClassColumn = "A"
Const xClassStudentsCol = "B"
Const xFirstClassRow = 2
'these have to do with
'this sheet (sheetZ)
Const zClassColumn = "A"
Const zFirstClassRow = 2
'end of user defined constants
Dim xSheet As Worksheet
Dim xSheetClassList As Range
Dim anyxSheetClass As Range
Dim offsetToStudentCount As Integer
Dim zRow As Long
Dim zBaseCell As Range

'
'once you are sure it is working,
'remove the apostrophe from the beginning of
'the next instruction to improve speed
'Application.ScreenUpdating = False

'start by erasing old entries on SheetZ
'and rebuilding the labels in row 1
Cells.ClearContents
Range("A1") = "Class"
Range("B1") = "No. Students"
Set xSheet = ThisWorkbook.Worksheets(sheetXName)
If xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Row _
< xFirstClassRow Then
'no classes on SheetX, do nothing
Set xSheet = Nothing
Exit Sub
End If
Set xSheetClassList = xSheet.Range(xClassColumn & _
xFirstClassRow & ":" & _
xSheet.Range(xClassColumn & Rows.Count).End(xlUp).Address)
offsetToStudentCount = Range(xClassStudentsCol & 1).Column - _
Range(xClassColumn & 1).Column
Set zBaseCell = Range(zClassColumn & zFirstClassRow)
zRow = 0
For Each anyxSheetClass In xSheetClassList
If anyxSheetClass.Offset(0, _
offsetToStudentCount) > 0 Then
zBaseCell.Offset(zRow, 0) = anyxSheetClass
zBaseCell.Offset(zRow, offsetToStudentCount) = _
anyxSheetClass.Offset(0, offsetToStudentCount)
zRow = zRow + 1
End If
Next
'housekeeping
Set xSheetClassList = Nothing
Set xSheet = Nothing
Set zBaseCell = Nothing
End Sub


:

Dear Excel experts

I have a workbook with Sheet X and Z.

On Sheet X, there are these coloums

Class Number of Students Male Female

AA 10 3 7
YY
ZZ 0
NN
OO 20 5 15

(More data at a later date)

How do I have Excel 2003 fill data on Sheet Z, so that it will Print out
those Classes that have number of students (ignore zeros and null)?

Result:

Class Number of Students
AA 10
OO 20

Please remember that Sheet Z will be added with more data as time goes by
and I want SHeet Z be able to keep spitting out Classes that have students.

Thank you for your advice.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top