Menu
Im doing an app with Excel VBA and I need to copy some ranges from different Worksheet in a variable.
All the ranges have the same number of columms but different number of rows. I was thinking on making like a matrix. Just append the UsedRange from every Sheet one down the other... but no way
For i = 1 To wbDataFile.Sheets.Count
Set wsCPDataFile = wbDataFile.Sheets(wbDataFile.Sheets(i).Name)
If Not DataRng Is Nothing Then
Set DataRng= DataRng (Append??)
Else
' the first valid cell becomes rng2
Set DataRng = wsCPDataFile.UsedRange
End If
Next I
Data = DataRng
966
asked Aug 21 '14 08:08
Combining same ranges from multiple sheets - 3D formulaClick the tab of the first worksheet. While holding the Shift key, click the tab of the last worksheet. Select the range that you want to combine in all of the sheets. Type the closing parenthesis and press the Enter key.
Click the first cell and press Shift while you click the last cell in the range you want to merge. Important: Make sure only one of the cells in the range has data. Click Home > Merge & Center. If Merge & Center is dimmed, make sure you're not editing a cell or the cells you want to merge aren't inside a table.
When writing formulas we sometimes need to create references to multiple cells or ranges. One quick way to do this is by holding the Ctrl key and then selecting the cells or ranges. Excel will automatically add the commas between the range references in the formula.
To do an intermediate append, select the arrow next to the command, and then select Append Queries as New. The Append dialog box appears. Decide the number of tables you want to append: Select Two tables, and then select the second table in the drop down list box to append.
Use Union. Like so:
Sub jzz()
Dim rng As Range
Set rng = Range("A1")
Set rng = Union(rng, Range("A2"))
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With