Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel: VBA to select dynamic range

Posted on 2015-02-16
6
Medium Priority
?
357 Views
Last Modified: 2016-02-10
Hi,

I have a spreadsheet that has a variable range of populated text cells in column B. This range would always start at B7 - and could extend to B200.

I would like to select this variable cell range via VBA, then copy it to cache.

I have a sample spreadsheet attached.


Cheers,
ee-variablerange.xlsx
0
Comment
Question by:dabug80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 2000 total points
ID: 40613220
Hi

You can try like this:
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B7:B" & LR).SpecialCells(xlCellTypeConstants, 23).Copy

Open in new window

ee-variablerange-1.xlsm
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40613245
if you want to copy from B7 to the last cell in the rage, including the empty cells, then take the below code:
Dim Mylastrow As Long
Mylastrow = Range("B:B").Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
Range("B7:B" & Mylastrow).Copy

Open in new window

All cells in range
If you only want the cells with values:
Dim Mylastrow As Long
Mylastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("B7:B" & Mylastrow).SpecialCells(xlCellTypeConstants, 2).Copy

Open in new window

Cells with values
0
 
LVL 1

Author Comment

by:dabug80
ID: 40613303
Thanks. This is great. At the end of the code, is it possible to deselect the cells, yet still have the copied cells in cache?
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 11

Expert Comment

by:Wilder1626
ID: 40613326
I don't thing it is possible. well from what i know.
0
 
LVL 1

Author Closing Comment

by:dabug80
ID: 40613422
OK Thanks. I found a 'work around' to remove the end range selection. I added a message box, then cleared the copy cache/selection afterwards. Here's the final code.

Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B7:B" & LR).SpecialCells(xlCellTypeConstants, 23).Copy
MsgBox "Range saved. Paste into Outlook."
Application.CutCopyMode = False

Open in new window


Thanks for the help
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40614540
I'm glad you found a work around and glad i was able to help.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

636 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