Selecting multiple ranges

I am trying to copy selected columns from ne shet to another to print
them using VBA in Excel. I have found that when you select multiple
ranges in Excel it seems that if you try to unselect one of the
selected columns you get another selected range. So if I select columns
A to D and columns E to H, I have 2 ranges. Then I try to unselect
column B ( in fact it doesn't unselect but jusr remains highlighted) as
I don't want it I get another range with just column B in. When I come
to copy the selected ranges I get col B twice. Is there a way round
this?
john

Hello John,

You can select multiple ranges in code 2 ways... Theses examples select the entire columns of A and C through H.

Example 1:
Range("A:A,C:H").Select

Exmaple 2:
Application.Union.Range("A:A", "C:H").Select

Sincerely,
Leith Ross

Trying to copy non-contiguous blocks of cells is never easy. For example if
you select columns A and C (don't select B) and copy them and then try to
paste to a new worksheet, material gets pasted into A & B, not A & C. I
suggest that you:

1. copy with a series of contiguous copy/pastes to the second sheet
or
2. hide the columns you don't want to print and avoid copying at all
--
Gary's Student


"[email protected]" wrote:

> I am trying to copy selected columns from ne shet to another to print
> them using VBA in Excel. I have found that when you select multiple
> ranges in Excel it seems that if you try to unselect one of the
> selected columns you get another selected range. So if I select columns
> A to D and columns E to H, I have 2 ranges. Then I try to unselect
> column B ( in fact it doesn't unselect but jusr remains highlighted) as
> I don't want it I get another range with just column B in. When I come
> to copy the selected ranges I get col B twice. Is there a way round
> this?
> john
>
>

I am trying to allow users to print only selected columns from a
datasheet. So they select the columns they want to print and tey are
pasted to a hidden worksheet contiguously and printed. I am using:
cntA = Selection.Areas.Count to count the different areas selected.
Then I can work out how many columns each area has b
icnt2 = Selection.Areas(icnt1).Columns.Count
then knowing what is selected I can copy it to the new sheet in order.
But the "deselected" column appears as a further Selection.area which
screws it up.
Is there a way to tell if a column is selected?
John

I've found that it's always a pain to get headers/footers correct. And
columnwidths and rowheights to do what I want.

One alternative is to copy the worksheet to a new workbook, change everything to
values, delete the columns, print the new worksheet and then close the new
workbook without saving.

If you think that's something you want to try:

Option Explicit
Sub testme()
Dim tempWks As Worksheet
Dim curWks As Worksheet
Dim rng As Range
Dim iCol As Long
Dim LastCol As Long

Set curWks = Worksheets("sheet1")
curWks.Select

Set rng = Nothing
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a bunch of cells", Type:=8)
On Error GoTo 0

If rng Is Nothing Then
Exit Sub 'cancel
End If

With curWks
If rng.Parent.Name <> .Name Then
MsgBox "Please select something on: " & .Name
Exit Sub
End If

Set rng = Intersect(.Rows(1), rng.EntireColumn).EntireColumn
.Copy 'to a new workbook
End With

Set tempWks = ActiveSheet
With tempWks
With .UsedRange
.Copy
.PasteSpecial Paste:=xlValues
End With
LastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
For iCol = LastCol To 1 Step -1
If Intersect(curWks.Columns(iCol), rng) Is Nothing Then
.Columns(iCol).Delete
End If
Next iCol

'save some trees
.PrintOut preview:=True

'uncomment when you're done testing
.Parent.Close savechanges:=False

End With
End Sub


[email protected] wrote:
>
> I am trying to allow users to print only selected columns from a
> datasheet. So they select the columns they want to print and tey are
> pasted to a hidden worksheet contiguously and printed. I am using:
> cntA = Selection.Areas.Count to count the different areas selected.
> Then I can work out how many columns each area has b
> icnt2 = Selection.Areas(icnt1).Columns.Count
> then knowing what is selected I can copy it to the new sheet in order.
> But the "deselected" column appears as a further Selection.area which
> screws it up.
> Is there a way to tell if a column is selected?
> John

--

Dave Peterson

Thanks I will give it a go.