Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Extend Selected Cells in Excel VBA

Posted on 2012-09-07
6
Medium Priority
?
489 Views
Last Modified: 2012-09-07
Hi All,

Hopefully this is an easy answer, although searching Google and EE didn't help (maybe I was using the wrong search terms).

Anyway, using VBA I've ended up with a selection of cells from a Pivot Table. I would like to know how to extend the selection to include one more cell to the right please.

e.g. say I have A1to A10 selected, how would I programmatically extend the selection by one cell to the right so I end up with A1 to A11 selected?

I wish to add this extension selection to the PivotFields line below please.

    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)
    pt.PivotFields("Item").DataRange.Select

Open in new window

Many thanks
0
Comment
Question by:jarrah10
  • 4
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38376121
selection.resize(selection.rows, selection.columns+1).select
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38376123
Or simply

selection.resize(, selection.columns+1).select

You have to add this line after the select statement given in the question.

Alternatively you can replace your select statement with this one
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38376131
pt.PivotFields("Item").DataRange.resize(,pt.PivotFields("Item").DataRange.columns+1).Select
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1600 total points
ID: 38376138
Sorry, correction

selection.resize(, selection.columns.count+1).select

pt.PivotFields("Item").DataRange.resize(,pt.PivotFields("Item").DataRange.columns.count+1).Select
0
 
LVL 36

Expert Comment

by:Norie
ID: 38376145
Or this:
Range(Selection, Selection.Offset(,1)).Select

Open in new window

0
 
LVL 1

Author Closing Comment

by:jarrah10
ID: 38377013
Perfect! Thanks very much. :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question