Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
that's pretty bizarre. did it to me too.
first i thought it may be the differing rows in the 3 areas, but no. i'm really not sure what to do. when you check the number of rows in rng, it returns 15 - the rows in area 1 of the range. i guess you need to sum rows in all 4 areas then loop through all 4 areas with the aydata loop nested inside sorry, stumped and limited time today -vdj "grahamhurlburt" <grahamhurlburt.218tsm_1136601602.6527@excelforu m-nospam.com wrote in message news:grahamhurlburt.218tsm_1136601602.6527@excelfo rum-nospam.com... Thanks voodooJoe, you're script works perfectly on the table examples! Sorry I didn't give you more info.. I thought someone would just point me in the right direction and give me a kick..I was really excited to see a working script made up! However, putting it into action on the actual spreadsheet has created some issues..you guys seem to know everything tho.. The ranges for the actual tables are B18:D32, B52:D83, B104:D135. There is a bunch of other stuff in between the tables, merged cells and whatnot.. Here is the script I am using, it keeps dropping the rows from the second and third tables..any ideas? Sub X() 'id your range Set rng = Range("B18:D32,B52:D83,B104:D135") 'set range values to array & clear the range 'the values are in the array aydata = rng rng.ClearContents 'this will track where to put the data desrow = 1 'loop thru the array For i = LBound(aydata) To UBound(aydata) 'if the value in the 3rd column is not equal to zero If aydata(i, 3) < 0 Then 'write the row to the spreadsheet For j = 1 To 3 rng.Cells(desrow, j) = aydata(i, j) Next j 'skip to next row UNLESS this is the 4th row in the block, then skip 2 rows If desrow / 15 = Int(desrow / 15) Then desrow = desrow + 20 Else desrow = desrow + 1 End If Next i End Sub -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=498835 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
I have tables running down a spreadsheet that are 3 columns across lik so: dd-008 etc are just names of boards. | dd-008 | acrylic | 0 | | dc-004 | acrylic | 20 | | db-012 | merlex | 40 | | da-006 | acrylic | 0 | There are breaks between the tables..like this one | dd-004 | acrylic | 10 | | dc-002 | merlex | 0 | | da-010 | merlex | 20 | | da-016 | acrylic | 5 | My question is, without deleting the row, is there anyway to delete th values in the rows with a zero and move all the values below it up? For example, the two tables would then look like this: | dc-004 | acrylic | 20 | | db-012 | merlex | 40 | | dd-004 | acrylic | 10 | | da-010 | merlex | 20 | There are breaks between the tables..like this one | da-016 | acrylic | 5 | I have worked hours on this with no luck...any ideas -- grahamhurlbur ----------------------------------------------------------------------- grahamhurlburt's Profile: http://www.excelforum.com/member.php...fo&userid=2987 View this thread: http://www.excelforum.com/showthread.php?threadid=49883 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
Are there the same number of rows in each table, and between each
table? That is, do you have four rows in a table, maybe one blank row, four more table rows, one blank row, and so forth? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
On Fri, 6 Jan 2006 14:13:02 -0600, grahamhurlburt
<grahamhurlburt.218bz0_1136578515.2777@excelforu m-nospam.com wrote: I have tables running down a spreadsheet that are 3 columns across like so: dd-008 etc are just names of boards. | dd-008 | acrylic | 0 | | dc-004 | acrylic | 20 | | db-012 | merlex | 40 | | da-006 | acrylic | 0 | There are breaks between the tables..like this one | dd-004 | acrylic | 10 | | dc-002 | merlex | 0 | | da-010 | merlex | 20 | | da-016 | acrylic | 5 | My question is, without deleting the row, is there anyway to delete the values in the rows with a zero and move all the values below it up? For example, the two tables would then look like this: | dc-004 | acrylic | 20 | | db-012 | merlex | 40 | | dd-004 | acrylic | 10 | | da-010 | merlex | 20 | There are breaks between the tables..like this one | da-016 | acrylic | 5 | I have worked hours on this with no luck...any ideas? It's easy to filter out the blank and non-zero rows. But I don't understand the logic in the filtered list having a break prior to the last row ??? In any event, label your columns. Let's call them: Label Type Quantity Then select the ENTIRE table, including the breaks between them. Then Data/Filter/Autofilter If you just want to remove the 0's, click on the down arrow next to quantity and select Custom; then Quantity does not equal 0 Label Type Quantity dc-004 acrylic 20 db-012 merlex 40 dd-004 acrylic 10 da-010 merlex 20 da-016 acrylic 5 If you also want to filter the breaks, with a column other than quantity, click on the down arrow and select "non-blanks" Label Type Quantity dc-004 acrylic 20 db-012 merlex 40 dd-004 acrylic 10 da-010 merlex 20 da-016 acrylic 5 --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
graham -
you left out some key info, but I made assumptions: - are the blocks you want to keep always 4 rows? - are the spaces between blocks always teh same number of rows? I assumed yes. The code below will do what you want. turn off screen updating to move faster suggest you insert a line to copy the data in case the macro crashes or get inturrupted - that way you don't lose it cheers - voodooJoe Sub X() 'id your range Set rng = Selection 'set range values to array & clear the range 'the values are in the array aydata = rng rng.ClearContents 'this will track where to put the data desrow = 1 'loop thru the array For i = LBound(aydata) To UBound(aydata) 'if the value in the 3rd column is not equal to zero If aydata(i, 3) < 0 Then 'write the row to the spreadsheet For j = 1 To 3 rng.Cells(desrow, j) = aydata(i, j) Next j 'skip to nuext row UNLESS this is the 4th row in the block, then skip 2 rows If desrow / 4 = Int(desrow / 4) Then desrow = desrow + 2 Else desrow = desrow + 1 End If Next i End Sub "grahamhurlburt" <grahamhurlburt.218bz0_1136578515.2777@excelforu m-nospam.com wrote in message news:grahamhurlburt.218bz0_1136578515.2777@excelfo rum-nospam.com... I have tables running down a spreadsheet that are 3 columns across like so: dd-008 etc are just names of boards. | dd-008 | acrylic | 0 | | dc-004 | acrylic | 20 | | db-012 | merlex | 40 | | da-006 | acrylic | 0 | There are breaks between the tables..like this one | dd-004 | acrylic | 10 | | dc-002 | merlex | 0 | | da-010 | merlex | 20 | | da-016 | acrylic | 5 | My question is, without deleting the row, is there anyway to delete the values in the rows with a zero and move all the values below it up? For example, the two tables would then look like this: | dc-004 | acrylic | 20 | | db-012 | merlex | 40 | | dd-004 | acrylic | 10 | | da-010 | merlex | 20 | There are breaks between the tables..like this one | da-016 | acrylic | 5 | I have worked hours on this with no luck...any ideas? -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=498835 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
Thanks voodooJoe, you're script works perfectly on the table examples! Sorry I didn't give you more info.. I thought someone would just point me in the right direction and give me a kick..I was really excited to see a working script made up! However, putting it into action on the actual spreadsheet has created some issues..you guys seem to know everything tho.. The ranges for the actual tables are B18:D32, B52:D83, B104:D135. There is a bunch of other stuff in between the tables, merged cells and whatnot.. Here is the script I am using, it keeps dropping the rows from the second and third tables..any ideas? Sub X() 'id your range Set rng = Range("B18:D32,B52:D83,B104:D135") 'set range values to array & clear the range 'the values are in the array aydata = rng rng.ClearContents 'this will track where to put the data desrow = 1 'loop thru the array For i = LBound(aydata) To UBound(aydata) 'if the value in the 3rd column is not equal to zero If aydata(i, 3) < 0 Then 'write the row to the spreadsheet For j = 1 To 3 rng.Cells(desrow, j) = aydata(i, j) Next j 'skip to next row UNLESS this is the 4th row in the block, then skip 2 rows If desrow / 15 = Int(desrow / 15) Then desrow = desrow + 20 Else desrow = desrow + 1 End If Next i End Sub -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=498835 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
1st table: 15 rows 19 row space 2nd table: 32 rows 20 row space 3rd table: 32 rows Also, would there be anyway to add a line in the function that if a value in the first column was longer than 10 characters it would not delete, regardless of the value in the third column? Thanks -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=498835 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
Any ideas? -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=498835 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
Could you put your table data into a contiguous area (either on the
same sheet or on a separate sheet) and refer to that data from your tables? For instance, if the table data is on a sheet named "Tables" then B18 refers to Tables!A1 (the formula in B18 would be "=Tables!A1"), C18 refers to Tables!B1, D18 references Tables!C1. Row 19 in your original table area references row 2 on Tables, row 20 references Tables row 3, etc. Original table row 32 would reference row 15 on the Tables sheet. Then you have your gap, and your second table beginning with B52 would reference Tables row 16, the data on row 53 refers to Tables row 17, and so on. You can use a modified (and now simpler) version of voodooJoe's code to massage the contiguous table data on the Table sheet, moving data up to fill in rows that become blank, and the original table areas, by always reading the same rows on the Tables sheet, are automatically filled in properly. (Google's been coughing up hairballs trying to post my comments lately. You may already have seen a version of this comment, which I posted last night. If so, sorry. But I haven't seen it yet.) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
If you're moving your information between tables, can you lump the
tables together on another worksheet and refer to those cells from the current tables? That is, B18 in your table area would reference A1 in the contiguous table, C18 would reference B1, D18 would ref C1. B19 references A2, C19 refs B2, D19 refs C2, and so on. B52 would reference the row after the one B32 references. This way you should be able to easily modify voodooJoe's code to work on the contiguous data and have it display correctly in your tables. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
I'm not even sure this is possible anymore!
Linc you genious...solved all the problems...:) and thanks again voodoojoe, the script runs perfectly now! -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=498835 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Don't want to hide anymore | Excel Discussion (Misc queries) | |||
Why doesn't it work anymore? | Excel Discussion (Misc queries) | |||
I'm not even sure if this is possible anymore! | Excel Worksheet Functions | |||
Functions don't work anymore! | Excel Programming | |||
it's not working anymore, please help! | Excel Programming |