Can a range be copied if it varies in size?

[TheMilkGuy]

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]

Your request/query seems a little confusing, however, if the crux of th
matter is you want to hide the rows where column B shows a zero then us
this in a standard modul
Code
-------------------
Public Sw As Lon
Sub HideRows(
Dim Rng As Range, MyCell As Rang
Set Rng = Sheets("Sheet1").Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row
If Sw = 1 The
Rng.Rows.Hidden = Fals
Sw =
Exit Su
End I
For Each MyCell In Rn
If MyCell.Value = 0 The
MyCell.Rows.Hidden = Tru
Sw =
End I
Next MyCel
End Su

-------------------
Add a button to your toolbar and assign the macro, one click will hid
all the rows that have 0 in column b, clicking again will show all th
rows again, click again and the process of hiding rows with a 0 i

Hi folks, I am running Excel 2007

I have a small table that gets generated based on up to 10 Point1 t
Point2 cells

The table calculates distance from 1 to 2 (and some other sundry dat
as well in subsequent columns

Problem is that if I'm only going to Point6 I'm left with 4 more row
of data. The Point6-Point7 row has 6 as a Column A start point but
Zero in Column B as an end point

This table is just a stepping-stone before all of this data makes it
way to the Sheet that will be printed. How might I go about copyin
ONLY the rows in which Column B does not display Zero, whilst makin
it possible to copy them all should the next generated set of point
use all 10? Or just 2

The range I am working with is A30:T3

Many thanks
Crai

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[TheMilkGuy]

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]

How would you want to trigger the code without buttons?, anyway for no
here's the code that you need, it does as before but now copies al
visible in the range thats left to a new sheet, try it :

Code
-------------------
Public Sw As Lon
Sub HideRows(
Dim Rng As Range, MyCell As Rang
Set Rng = Sheets("Sheet1").Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row
If Sw = 1 The
Rng.Rows.Hidden = Fals
Sw =
Exit Su
End I
For Each MyCell In Rn
If MyCell.Value = 0 The
MyCell.Rows.Hidden = Tru
Sw =
End I
Next MyCel
Rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1"
End Su
-------------------

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 th
cells with a "0" in Column B, I would like to take all of the cell
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
CYBG CYRJ 30000 300 260 53 283 28
CYRJ 0 30000 30

Since the second row is unnecessary, only row 1 would need to b
copied to my other worksheet

Also, is it possible to make this work without any buttons? I ca
live with them, don't get me wrong... Just curious

Sorry to hijack so much of your time

Cheers
Crai

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[TheMilkGuy]

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