Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a range be copied if it varies in size?
Hi folks, I am running Excel 2007.
I have a small table that gets generated based on up to 10 Point1 to Point2 cells. The table calculates distance from 1 to 2 (and some other sundry data as well in subsequent columns) Problem is that if I'm only going to Point6 I'm left with 4 more rows of data. The Point6-Point7 row has 6 as a Column A start point but a Zero in Column B as an end point. This table is just a stepping-stone before all of this data makes its way to the Sheet that will be printed. How might I go about copying ONLY the rows in which Column B does not display Zero, whilst making it possible to copy them all should the next generated set of points use all 10? Or just 2? The range I am working with is A30:T39 Many thanks! Craig |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a range be copied if it varies in size?
Your request/query seems a little confusing, however, if the crux of the matter is you want to hide the rows where column B shows a zero then use this in a standard module Code: -------------------- Public Sw As Long Sub HideRows() Dim Rng As Range, MyCell As Range Set Rng = Sheets("Sheet1").Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row) If Sw = 1 Then Rng.Rows.Hidden = False Sw = 0 Exit Sub End If For Each MyCell In Rng If MyCell.Value = 0 Then MyCell.Rows.Hidden = True Sw = 1 End If Next MyCell End Sub -------------------- Add a button to your toolbar and assign the macro, one click will hide all the rows that have 0 in column b, clicking again will show all the rows again, click again and the process of hiding rows with a 0 in column b will happen againTheMilkGuy;449725 Wrote: Hi folks, I am running Excel 2007. I have a small table that gets generated based on up to 10 Point1 to Point2 cells. The table calculates distance from 1 to 2 (and some other sundry data as well in subsequent columns) Problem is that if I'm only going to Point6 I'm left with 4 more rows of data. The Point6-Point7 row has 6 as a Column A start point but a Zero in Column B as an end point. This table is just a stepping-stone before all of this data makes its way to the Sheet that will be printed. How might I go about copying ONLY the rows in which Column B does not display Zero, whilst making it possible to copy them all should the next generated set of points use all 10? Or just 2? The range I am working with is A30:T39 Many thanks! Craig -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124504 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a range be copied if it varies in size?
Hi Simon,
Thanks for the code, I realize my request is a bit wordy... Your solution is exactly half of what I need :) instead of hiding the cells with a "0" in Column B, I would like to take all of the cells that were NOT to be hidden and copy them to another worksheet. Here's an example of the chart if only One row was necessary: A B C D E F G H CYBG CYRJ 30000 300 260 53 283 287 CYRJ 0 30000 300 Since the second row is unnecessary, only row 1 would need to be copied to my other worksheet. Also, is it possible to make this work without any buttons? I can live with them, don't get me wrong... Just curious. Sorry to hijack so much of your time. Cheers! Craig |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a range be copied if it varies in size?
How would you want to trigger the code without buttons?, anyway for now here's the code that you need, it does as before but now copies all visible in the range thats left to a new sheet, try it :) Code: -------------------- Public Sw As Long Sub HideRows() Dim Rng As Range, MyCell As Range Set Rng = Sheets("Sheet1").Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row) If Sw = 1 Then Rng.Rows.Hidden = False Sw = 0 Exit Sub End If For Each MyCell In Rng If MyCell.Value = 0 Then MyCell.Rows.Hidden = True Sw = 1 End If Next MyCell Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1") End Sub -------------------- TheMilkGuy;450148 Wrote: Hi Simon, Thanks for the code, I realize my request is a bit wordy... Your solution is exactly half of what I need :) instead of hiding the cells with a "0" in Column B, I would like to take all of the cells that were NOT to be hidden and copy them to another worksheet. Here's an example of the chart if only One row was necessary: A B C D E F G H CYBG CYRJ 30000 300 260 53 283 287 CYRJ 0 30000 300 Since the second row is unnecessary, only row 1 would need to be copied to my other worksheet. Also, is it possible to make this work without any buttons? I can live with them, don't get me wrong... Just curious. Sorry to hijack so much of your time. Cheers! Craig -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=124504 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can a range be copied if it varies in size?
Code's great, but the cells that got copied to Sheet2 almost all
showed up as #REF! Have I done something wrong? Changing the code to xlCellTypeVisible to xlCellTypeAllFormatConditions produces a 1004 error. Craig On Aug 12, 4:09*pm, Simon Lloyd wrote: How would you want to trigger the code without buttons?, anyway for now here's the code that you need, it does as before but now copies all visible in the range thats left to a new sheet, try it :) Code: -------------------- * * Public Sw As Long * Sub HideRows() * Dim Rng As Range, MyCell As Range * Set Rng = Sheets("Sheet1").Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row) * If Sw = 1 Then * Rng.Rows.Hidden = False * Sw = 0 * Exit Sub * End If * For Each MyCell In Rng * If MyCell.Value = 0 Then * MyCell.Rows.Hidden = True * Sw = 1 * End If * Next MyCell * Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1") * End Sub -------------------- TheMilkGuy;450148 Wrote: Hi Simon, Thanks for the code, I realize my request is a bit wordy... Your solution is exactly half of what I need :) instead of hiding the cells with a "0" in Column B, I would like to take all of the cells that were NOT to be hidden and copy them to another worksheet. Here's an example of the chart if only One row was necessary: A B C D E F G H CYBG CYRJ 30000 300 260 53 283 287 CYRJ 0 30000 300 Since the second row is unnecessary, only row 1 would need to be copied to my other worksheet. Also, is it possible to make this work without any buttons? I can live with them, don't get me wrong... Just curious. Sorry to hijack so much of your time. Cheers! Craig -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=124504 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum of a range that varies | Excel Discussion (Misc queries) | |||
Named-range source-data for pie charts on copied worksheets | Charts and Charting in Excel | |||
V look up and data that goes out when copied in range | Excel Discussion (Misc queries) | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) |