Hi, thank you so much for your video, it was very helpful though I need something a bit more complex if you can help me I will really appreciate it. (For everyone :)) I will paint kind of table (Ai - Aj & Bi-Bj & Ci-Cj) based on the text on the Di for example ; If D4 has the text "red" then paint red from A4 to A12 & B4 to B12 & C4 to C12. for every one of the loop i & j will increase 10 by 10. Another 2 example to understand better; If D1 = "White" select from A1 to A9 & B1 to B9 & C1 to C9 paint them as white color (I will add 7 possible color for every one of Di, see last example down below) If D11 = "Yellow" select from A11 to A19 & B11 to B19 & C11 to C19 paint them as yellow color. If D21 = "Blue" select from A21 to A29 & B21 to B29 & C21 to C29 paint them as blue color. If D21 = "orange" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as orange color If D21 = "red" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as red color If D21 = "blue" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as blue color If D21 = "yellow" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as yellow color If D21 = "green" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as green color If D21 = "white" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as white color
Thank you again. My actual data is located on Sheet2 while on Sheet1 I have my buttons. So on Sheet2 I have a big table full of data. With a click of a button (which is located on Sheet1) I want to be able to select all cells in column, let's say M. And with a click of another button I want o be able to select all cells in column Z etc. Your codes work perfect in selecting the entire table, even if there is a gap with empty blank cells, your code still manages to highlight the entire table. However, I was wondering if its possible to select all cells from column M?
@@StrategizeFinancialModelling No no. I want to be able to select data ranges in just one column. My attempts fail because excel stops whenever there is a blank cell in-between the data. I want excel to do the same think as you did with your code, but instead selecting all data in a table, it should only select data in a column as specified by me.
Hi Enkhtur are you able to send a sample or a screenshot to strategizefm@gmail.com ? Sorry I want to make sure I fully understand what you are trying to achieve
@@StrategizeFinancialModelling Hello, thank you for your response. In your video, when you go to 07:59, I want to be able to select all data in column H (cells H4 through to H24) - just wondering how do I go about doing that?
How can I extract from any given number of sheets both fixed and variable data. In this specific case is information out of packing lists. I need to extract fix data as the date and the consecutive number of packing list. The variable data consists in what was shiped in that specific paking list. On that part I need the parts sent and its quantity. Just in the January Excel Document there are 256 sheets. Each one of the a different packing list. The ideal outcome would be Date, Paking list, Part 1, Quantity, Sheet number. Hope you can help me.
Ok, so then I want to use that dynamic range in a Pivot Table (which I am also writing a macro for)...how do you write VBA to use the dynamic range for the Pivot?
What I need is - if you go to 07:509 in your video, I want to be able to select all data in column H (cells H4 through to H24) - just wondering how do I go about doing that?
Hi Jibreelinater thanks for the message. To find say column X, change the dynamic range code to this: ws.Range(startCell, ws.Cells(lastRow, "X")).Select
Hi Enkhtur, thanks for your question. Do see the piece of code: ' Set objects Set ws = Sheet1 Set startCell = Range("B4") If the range is say on another sheet (i.e. Sheet2), then change the above to this: ' Set objects Set ws = Sheet2 Set startCell = Range("B4") Hope that helps. regards Simon
I have a list of customer ticket # that needs to be QA, using a dynamic number i pull in ticket # and other information from another sheet. I am looking how can i only select number of rows as mentioned in the dynamic number. The code in the video returns blank cells at the bottom of the list which has formula in it Cell B9 Number of ticket to review Cell B10 40 Cell B11 Cell B12 ticket 1 Cell B52 ticket 40 so it should only select Cells B9 to M52, Column B to M also has data pulled from other sheet)
Thank you for your input. You are correct, one can use Ctrl + *, however video was intended to highlight a VBA macro approach - if someone needs to obtain a range for the purposes of a macro.
I am struggling with one situation, I want to make a macro to find cell basis value in particular cell, however the value in that particular cell is dynamic. Whenever I am running macro, macro picks up previous value....please help
1. In one folder , we will put all the aggregate csv, System should prepare the response time and error report from all csv 2. if same aggregate csv , e.g. if we have done testing from 2 load injector ,1000 load for a module And 500 each from 1 server , system should combine all the same module aggregate report and will prepare final report. 3. Tps should automatically prepare for same. 4. Pass and fail transaction % should also generate. Plz help I need itt,,,,
Hi Jaime, what is the name of your worksheet where you're trying to run the macro? In this example, the sheet was called Sheet1, hence we used: Set ws = Sheet1 Set startCell = ws.Range("A2")
'Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'On Error Resume Next 'Application.ScreenUpdating = True 'With Sheet1 'Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault Range("A1:A3").AutoFill Destination:=Range("A1:A60") ' Range("M10").Formula = "=IF(OR(ISBLANK(D6),ISBLANK(E6),ISBLANK(F6),ISBLANK(C6)),(FG65+D6-E6-F6))" Range("A1:A3").AutoFill Destination:=Range Balance("A61") ("A1+A60) 'Application.ScreenUpdating = False 'End If 'End With 'End Sub can pls help me correct
Bro i got Run time error 1004 Method 'Range' of object'_worksheet' failed. On the line ws. Range(startcell, ws. cells(lastrow, lastcol)). Select. Plz advize
@@StrategizeFinancialModelling Thank you so much its working fine now. And could please share me the macro coding tobshow the running status of the macro using progress bar for the entire macro
thanks guys i was looking for something similar for a long time . Appreciate
Glad it was helpful!
This week I learned the short version:
Sub dynamicRange()
Dim R As Range
Set R = Range("B4").CurrentRegion
R.Select '
This is brilliant! You just saved the day!!
Very good! you should put the code in the description to avoid the viewer needing to type everything from scratch
Hi Jonathan, thanks for your feedback, we have gone one step further by sharing the workbook at this link:
spreadsheetvault.com/vba-dynamic-ranges/
Awesome content. Very clear explanations 👍
Lovely 💞
Just what I was looking for. Thanks.
Hi, thank you so much for your video, it was very helpful though I need something a bit more complex if you can help me I will really appreciate it. (For everyone :))
I will paint kind of table (Ai - Aj & Bi-Bj & Ci-Cj) based on the text on the Di for example ; If D4 has the text "red" then paint red from A4 to A12 & B4 to B12 & C4 to C12.
for every one of the loop i & j will increase 10 by 10. Another 2 example to understand better;
If D1 = "White" select from A1 to A9 & B1 to B9 & C1 to C9 paint them as white color (I will add 7 possible color for every one of Di, see last example down below)
If D11 = "Yellow" select from A11 to A19 & B11 to B19 & C11 to C19 paint them as yellow color.
If D21 = "Blue" select from A21 to A29 & B21 to B29 & C21 to C29 paint them as blue color.
If D21 = "orange" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as orange color
If D21 = "red" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as red color
If D21 = "blue" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as blue color
If D21 = "yellow" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as yellow color
If D21 = "green" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as green color
If D21 = "white" select from A31 to A39 & B31 to B39 & C31 to C39 paint them as white color
Excellent video, nicely presented
Thank you!!!
You're welcome!
Very useful and helpful. Thanks a lot. 👍
Thank you again. My actual data is located on Sheet2 while on Sheet1 I have my buttons. So on Sheet2 I have a big table full of data. With a click of a button (which is located on Sheet1) I want to be able to select all cells in column, let's say M. And with a click of another button I want o be able to select all cells in column Z etc. Your codes work perfect in selecting the entire table, even if there is a gap with empty blank cells, your code still manages to highlight the entire table. However, I was wondering if its possible to select all cells from column M?
Hi Enkhtur, thanks for your follow-up question. Do you mean, you need a macro to select the data ranges in column M and Z at the same time?
@@StrategizeFinancialModelling No no. I want to be able to select data ranges in just one column. My attempts fail because excel stops whenever there is a blank cell in-between the data. I want excel to do the same think as you did with your code, but instead selecting all data in a table, it should only select data in a column as specified by me.
Hi Enkhtur are you able to send a sample or a screenshot to strategizefm@gmail.com ? Sorry I want to make sure I fully understand what you are trying to achieve
@@StrategizeFinancialModelling Hello, thank you for your response. In your video, when you go to 07:59, I want to be able to select all data in column H (cells H4 through to H24) - just wondering how do I go about doing that?
How can I extract from any given number of sheets both fixed and variable data. In this specific case is information out of packing lists. I need to extract fix data as the date and the consecutive number of packing list. The variable data consists in what was shiped in that specific paking list. On that part I need the parts sent and its quantity. Just in the January Excel Document there are 256 sheets. Each one of the a different packing list.
The ideal outcome would be Date, Paking list, Part 1, Quantity, Sheet number.
Hope you can help me.
Hi Alberto is this a sample workbook that you can share?
That's all i finding, thank you!
Ok, so then I want to use that dynamic range in a Pivot Table (which I am also writing a macro for)...how do you write VBA to use the dynamic range for the Pivot?
Hi Juan is something like this help you? th-cam.com/video/_ABIteg-02M/w-d-xo.html
Amazing! I was able to implement this in my project, thank you
Great to hear!
What I need is - if you go to 07:509 in your video, I want to be able to select all data in column H (cells H4 through to H24) - just wondering how do I go about doing that?
Hi Enkhtur thank you for your question, please simply change the startCell cell syntax to this:
Set startCell = Range("H4")
@@StrategizeFinancialModelling Thank you! I will try it out shortly.
How would we modify this code if we wanted our range to extend only to a specific column. Ie; Not the last column, but a specific one?
Hi Jibreelinater thanks for the message.
To find say column X, change the dynamic range code to this:
ws.Range(startCell, ws.Cells(lastRow, "X")).Select
thank you, but i see its not working once you want to select a range that is located in a different sheet (same workbook), can you help me please?
Hi Enkhtur, thanks for your question. Do see the piece of code:
' Set objects
Set ws = Sheet1
Set startCell = Range("B4")
If the range is say on another sheet (i.e. Sheet2), then change the above to this:
' Set objects
Set ws = Sheet2
Set startCell = Range("B4")
Hope that helps.
regards Simon
Excellent, very helpful thank you.
I have a list of customer ticket # that needs to be QA, using a dynamic number i pull in ticket # and other information from another sheet. I am looking how can i only select number of rows as mentioned in the dynamic number. The code in the video returns blank cells at the bottom of the list which has formula in it
Cell B9 Number of ticket to review
Cell B10 40
Cell B11
Cell B12 ticket 1
Cell B52 ticket 40
so it should only select Cells B9 to M52, Column B to M also has data pulled from other sheet)
Nice tutorial, I liked the part on enabling/disabling features. But wouldn't "ctrl + *" do the trick in this case?
Thank you for your input. You are correct, one can use Ctrl + *, however video was intended to highlight a VBA macro approach - if someone needs to obtain a range for the purposes of a macro.
It selects cells down to row 332. Columns are selected correctly. Thanks
That's great Sarah! Happy to hear it worked for you. regards
Thank you, how do I exclude header selection?
Change this: Set startCell = Range("B5")
I am struggling with one situation, I want to make a macro to find cell basis value in particular cell, however the value in that particular cell is dynamic. Whenever I am running macro, macro picks up previous value....please help
Hi Vivek do you have a sample spreadsheet which you can share, in order for us to help you?
@@StrategizeFinancialModelling thank you....to which email ID should I forward the same?
@@vivekmusafir4492 strategizefm@gmail.com
How can you apply a function to the selected data?
Hi Max, do you mean you want to overwrite each cell in the selected range with a function formula?
here's the code:
Sub dynamicRange()
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.Sceenupdating = False
Dim startCell as Range, lastRow as Long, lastCol as long, ws As Worksheet
Set ws = Sheet1
Set Start Cell = Range("B4")
lastRow = ws.Cells(ws.rows.Count, startcell.column).end(xlUp).Row
lastCol = ws.cells(startCell.row, ws.columns.Count).end(xltoLeft.column
ws.range(startcell, ws.cells(lastRow, lastCol)).select
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.Sceenupdating = True
1. In one folder , we will put all the aggregate csv, System should prepare the response time and error report from all csv
2. if same aggregate csv , e.g. if we have done testing from 2 load injector ,1000 load for a module
And 500 each from 1 server , system should combine all the same module aggregate report and will prepare final report.
3. Tps should automatically prepare for same.
4. Pass and fail transaction % should also generate.
Plz help I need itt,,,,
Range("B4"). Currentregion. Select 👍
Been trying this, and must be doing something wrong.
Keep getting Run-time error '1004' Method 'Range' of object '_Worksheet' failed.
Any ideas?
Hi Jaime, what is the name of your worksheet where you're trying to run the macro?
In this example, the sheet was called Sheet1, hence we used:
Set ws = Sheet1
Set startCell = ws.Range("A2")
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'On Error Resume Next
'Application.ScreenUpdating = True
'With Sheet1
'Rows(lastrow).AutoFill Rows(lastrow).Resize(2), xlFillDefault
Range("A1:A3").AutoFill Destination:=Range("A1:A60")
' Range("M10").Formula = "=IF(OR(ISBLANK(D6),ISBLANK(E6),ISBLANK(F6),ISBLANK(C6)),(FG65+D6-E6-F6))"
Range("A1:A3").AutoFill Destination:=Range Balance("A61")
("A1+A60)
'Application.ScreenUpdating = False
'End If
'End With
'End Sub
can pls help me correct
Bro i got Run time error 1004
Method 'Range' of object'_worksheet' failed.
On the line
ws. Range(startcell, ws. cells(lastrow, lastcol)). Select.
Plz advize
Hi Rajesh, what is your startCell referencing? In the above video we used:
Set startCell = ws.Range("A2")
@@StrategizeFinancialModelling Thank you so much its working fine now.
And could please share me the macro coding tobshow the running status of the macro using progress bar for the entire macro