Forum Discussion
Issue with sheet in vba
Subodh_Tiwari_sktneer Thanks for clarifying the concept.
This is my sample code here.
Dim my_sheet2 As WorkSheet
Dim Lastrange As Long
Set my_sheet2 = ThisWokbook.Worksheets("Sheet1")
Lastrange = my_sheet2.Cells(Rows.Count, "F").End(xlUp).Row
my_sheet2.Range("E2").Formula = "=A:A & B:B"
my_sheet2.Range("E2").Copy Range("E3:E" & Lastrange) -> Copy instruction
my_sheet2.Range("E1").Value = "Final"
It's running fine but it's implementing formula in E2 column alone and it's not copying the formula for rest of the rows in E column.
Suppose my F column has 100 rows the new E column also need to be 100 rows with that formula implemented
The issue maybe the last row you are trying to find in column F. Maybe column F doesn't have any data in it?
Also, this can be written in the following way...
Dim my_sheet2 As Worksheet
Dim LastRow As Long
Set my_sheet2 = ThisWorkbook.Worksheets("Sheet1")
'This line will find the last row with data in column F
'Make sure that column F is populated with data so that LastRow is evaluated correctly
LastRow = my_sheet2.Cells(Rows.Count, "F").End(xlUp).Row
my_sheet2.Range("E1").Value = "Final"
my_sheet2.Range("E2:E" & LastRow).Formula = "=A2&B2"Thanks lot once again that solution works.
One more query from my side:
How to increase the performance of macro?
My macro deals with below data.
1) Copying of data from one workbook to another by file upload option.
2) Source excel will have minimum 1,20,000 rows & 30 columns and another source file will have minimum 50,000 rows & 28 columns.
3) Doing concatenation, vlooukp from another sheet data and then sting length reduce for particular column.
In this above case i did performance analysis which is taking almost for 34,000 rows it is taking 14 minutes of time to execute all the above steps.
My though here is like for 34,000 it is 14 min and when it comes to 1,20,000 rows almost an hour execution will be there.
Please suggest me some ideas to increase the performance.
Though your latest query is beyond the scope of your original question, all I can suggest is, read the source data into an array, manipulate the array as required and then write the whole array into the destination sheet at once.