Range in Merging Excel

Question

0.00/5 (No votes)

See more:

I have a code which I do not understand. Can someone explain to me this code:

VB

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

It can be found in this codes.

VB

<pre lang="vb">Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

Dim strWSName As String
strWSName = InputBox("Enter the file path of Excel Files to merge")

'change folder path of excel files here
Set dirObj = mergeObj.GetFolder(strWSName)

Set fileObj = dirObj.Files
For Each everyObj In fileObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and row 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

Application.CutCopyMode = False
bookList.Close
Next
End Sub

Posted 11-Dec-13 20:10pm

mitchiee1226

Add a Solution

Comments

Richard MacCutchan 12-Dec-13 3:46am

Try asking the person who wrote the code, or look it up in the documentation.

JosephBrittoE 16-May-23 9:37am

Can we add file Names in the first column? Any clues

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-12-12T09:30:00

Range.End Property[^] - Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW.

VB

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

Above code means:
Range("A65536").End(xlUp) - find first not empty cell in A column
.Row - get the number of row
and finally...
.Copy - copy "selected" range ;)

T-Maz · Accepted Answer · 2013-12-12T04:17:00

Solution 1

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy

this is how you dynamically select/copy a range in a worksheet.

it uses the last row that has data in column A to be the last cell in column IV

Posted 12-Dec-13 4:17am

T-Maz