Normally we code a range by writing it within "" as under
Range("A1:A10").SelectHowever if you put , between multiple ranges within the same line of the code you can select multiple ranges as given under
Range("A1:A10,D1:D10,F1:F10").Select
How to use Multiple Ranges in Excel VBA
Following macro code will explain you how to use multiple ranges in Excel VBASub Multiple_ranges() Range("A1:A10").Select MsgBox ("Single Range Selected") Range("A1:A10,D1:D10,F1:F10").Select MsgBox ("Multiple Ranges Selected") Selection.Copy Range("A11").Select ActiveSheet.Paste End Sub |
In the above macro we have selected range three different ranges
A1:A10
D1:D10
F1:F10
After selection we have copied the contents to Range A11. Another option for doing this was to copy the contents one by one for each of the range. However copy and paste is only one of the example you can use this for any desired action by you.
You can combine multiple ranges into one Range object using the Union method.
The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C3:D4, and then formats the combined ranges as bold.
|
Thanks for putting this types of tips...
ReplyDeletereally saves a lot of time..
Great Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
Thank you for this help!!!!
ReplyDeletehi, i have problem to select more 20 different range. for example. i have variable strR = "A1215:S1215,A1256:S1256,A1292:S1292,A1328:S1328,A1369:S1369,A1405:S1405,A1434:S1434,A1470:S1470,A1506:S1506,A1542:S1542,A1574:S1574,A1610:S1610,A1646:S1646,A1682:S1682,A1718:S1718,A1754:S1754,A1783:S1783,A1812:S1812,A1844:S1844,A1876:S1876,A1912:S1912,A1948:S1948" and i want to select all these range using range(strR).select but there is error. actually on that string there are 22 different range. but if i select under 20 different range there is no error. can anyone help me?
ReplyDeleteHave you got any solution on this? I am having similar issue.
DeletePrivate Sub Worksheet_Change(ByVal Target As Range)
ReplyDeleteDim rng As Range, r As Range
Set rng = Intersect(Target, Range("a2:a" & Rows.Count))
If rng Is Nothing Then Exit Sub
For Each r In rng
If Not IsEmpty(r.Value) Then
r.Copy Destination:=Sheets("sheet2").Range("a2")
End If
Next
Set rng = Nothing
End Sub
Hello Friends, the above program i need b2:b copies to b2 in next sheet and c2:c copies to c2 etc.... can anyone help me?
Hello, Yogesh Gupta and guys. The program of Yogesh I liked, but I want something similar.
ReplyDeleteI need use "Union" but for 2 range of 2 different worksheet.
I mean:
Set r1 = Sheets("Sheet1").Range("A1:B2")
Set r2 = Sheets("Sheet2").Range("C3:D4")
And now I need to use "Union" for those two ranges of Sheet1 and Sheet2.
Hello, friends.
ReplyDeleteAnother idea might be:
1. Copy the information from each worksheet and attach (paste) it to a third worksheet.
2. Print the third worksheet.
3. Delete the third worksheet, if possible.
Is it possible that this can be done?
Couldnt you just create a hidden third sheet which mirrors the data in needed format for printing, then macro-print it without having to unhide it?
DeleteHi Yogesh...
ReplyDeletecan u help me with this regard
i have a summary sheet... on a daily basis i will enter in the summary sheet... i have used sumproduct formulae in the respective months..separate sheets for separate months.. the data will be automatically be updated.. so if i enter tomo becoz of the formula the data on the previous date will not exist.. so i need a macro to paste spl values the data on the respective date in the respective month sheet based on the date entered in the summary sheet..
Additional tip:
ReplyDeleteMake sure that the number of characters in the Range parameter doesn't exceed 255 characters.
Peace.
Joel "2 x NBA Champion" Anthony
This comment has been removed by the author.
ReplyDeleteHi,
ReplyDeleteI need to know how to combine two files into one macro without the range, i mean that if the data differs in both files.
Hi,
ReplyDeleteI need to know how to combine two files into one macro without the range, i mean that if the data differs in both files.
I have 5 values in Sheet1 (sno., name, month, week, day and one variable value)
ReplyDeletewant to copy variable value in Sheet2 based on sno., name, month, week, day ?
tried to copy with command button but couldn't find how to select multiple range values
hi,
ReplyDeletei need to know how to copy the contents present in one sheet column wise and paste the data into the different sheets columnwise(i need to increment both column data while copying and increment the sheets while pasting it)
Hi guys and gals
ReplyDeleteI am trying to work out how to calculate the GST (for those who don’t know) using VBA
Points to note:
Sheet(“Data”) holds the list of items, the GST free price and if GST is applicable (some items do not attract GST here in Australia i.e. Bread Where a Cake (giggle) does attract GST.
Sheet(“Invoice data”) column J (or #10) is where I want the GST component to be listed if applicable.
GST is fixed at 10%
See image for the layouts for “Data” and "Invoice data” respectively
I want J (#10) to be just the 10% GST component and not the whole amount including GST depending if the item is attracting GST, and $0 if it does not, in the case of the bread
Can any of you Demi Gods able to help?
Ps anyone know how to add an image?
Hello Yogesh Gupta,
ReplyDeleteI am trying to develop script in Excel VB where I can read two different ranges , range "A" will be used for condition and range "B" will be used for finding the value which has repeated maximum number of times.
Here is the data below;
A B
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 12
1 116
1 117
0.95 118
Please help me !
Shiv
It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command.
ReplyDeleteMs excel training in chennai
Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating.
ReplyDeleteWeb Design Training
Hey, Your post is very informative and helpful for us.
ReplyDeleteIn fact i am looking this type of article from some days.
Thanks a lot to share this informative article.
hardware and networking course in hyderabad
Thanks for the great information , i was looking for this information from long.Great blog
ReplyDeletetally course in hyderabad
Thanks for sharing the details! thanks for sharing information,nice article.
ReplyDeletei would like to more information from your side!
please added more then tips!Am working in
websphere training in hyderabad
Learn Excel Basic to Adv. Level with 100% Placement offer. Luxury Lab, 10+ Years Experienced Trainer, Online & Classroom Both Training Available. Contact Number-+91-9311002620.
ReplyDeleteThere are Some Best Training Center for Advanced Excel regarding
Advanced excel training institute in Delhi
Advanced excel training institute in Noida
Đặt mua vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ
ve may bay tet 2021 gia re
giá vé máy bay sang canada
đường bay từ Việt Nam sang Pháp
đường bay từ Việt Nam sang Anh
mua vé máy bay giá rẻ
ve may bay di san francisco
giá vé máy bay đi Los Angesles
combo du lịch phú quốc giá rẻ
combo vinpearl nha trang 2021
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeletevé máy bay đi Mỹ giá rẻ
mua vé máy bay từ mỹ về vn
ve may bay tu anh ve viet nam
chuyến bay từ Pairs về TpHCM
bape
ReplyDeletesupreme clothing
kd13
ggdb
supreme