You're a damn good teacher for an old fart like me. The last programming I did was around the turn of the millennium. (except for a few modifications to old, existing ones)😂
Very well explained, i need to use this kind of selection to select some random data generated and delete it, then make the program in question(a simple accumulator) just start over on the cell it started. See i have developed a code but it just deletes the data and then keeps going on the next row instead of going back to the 1st row it started.
I have a workbook with 3 sheets. This workbook pulls dynamic data from a network, so while I can sometimes know what column I'm pulling from, I never know what row I'm on. Excel won't let me use the following line of code: Sheet3.Range(Cells(report_row, 2), Cells(report_row, 3)).Select I've also tried this without the Range method (just cells), but nope.
@@EverydayVBAExcelTraining Indexing the row wasn't the problem. It was getting range to play nice with variables. I ended up using this line of code: Set rng = Sheet3.Range("B" & report_row, "F" & report_row) Thankfully that's all I need it to do for now, but I have no idea how to get it to use my column variable.
You could add .cells to the range. Since your range has 1 row it would look something like this Range(...).cells(1,2) That would be the second column in the range
Love your VBA channel, just getting started on VBA. Was wondering how one can reference rows dynamically when wanting to hide rows? Since the design of the sheet is not complete I am inserting more rows and adding more items but when I write my code: eg. '************************************************* '** List of new Equipment Section '** Amplifier '** Reveals or hides row 56 '** Amplifier Reveals line: 56 '***************************************************** '** Room Technical Mods Section '** 1. There is an Amp, we show line 56 and lines 90-91 but hiding line 89 '** 2. There is no Amp, we hide lines 56 and lines 90-91 but show line 89 '** 3. Shows all lines for review - nothing hidden '***************************************************** If Not Application.Intersect(Range("N11"), Range(Target.Address)) Is Nothing Then Select Case Target.Value Case Is = "Amplifier": Rows("56").EntireRow.Hidden = False Rows("89").EntireRow.Hidden = True Rows("90:91").EntireRow.Hidden = False Case Is = "No Amplifier": Rows("89").EntireRow.Hidden = False Rows("56").EntireRow.Hidden = True Rows("90:91").EntireRow.Hidden = True Case Is = "Reveal All Amp": Rows("56").EntireRow.Hidden = False Rows("89:91").EntireRow.Hidden = False End Select End If If I have to insert a new line before line 56 or even after 56 but before 89-91 then my references which to me are a direct reference no longer work on the correct rows unless I change the numbers appropriately. Is there a way to dynamically assign a name to row and reference it that way in my formulas above? Note: this code works (after lots of research) but it is not in a module, it is a worksheet macro. I do this because I want to be able to select (based on drop down list which rows are shown and not shown for what I am designing. Many thanks to anyone who can help.
i have a sheet but date is changed every day in this sheet i make filter i select below data except first row and delete how can i make it in VBA know that data is changed every day
hello I don't know if you can solve this... I'm in a table with some cells hidden because a filter that left cells e25 and e13 visible (well the entire rows 25 and 23 visible)... if I'm moving up with the key arrows from cell e25 to e13 with a single keystroke I'm in e13... the upper arrow doesn't take me step by step to e24, e23, etc to reach e13... because they are hiden however... if I'm using a vba code range("e25").select selection.offset(-1.0).select it doesn't take me to e13 but e24 if I record a macro using the arrows the code is: activecell.offset(-12,0).range("a1").select how can I move by visible cells with a code?
I am going through a sheet where each employee has several rows of amounts. I want to copy the cells paste special "transpose" then delete the rows below the first of that employee. I have the first part but when it goes to delete rows. I doesn't move down below the line i want to keep.
Hi Chris I am just getting into VBA and Macros etc and I am glad I another excellent instructor. (I have a few of you who's videos I love and prefer among so many I have searched for, and you're in that list) So at the very end with the: Selection.Font.Bold = True Would that 'unbolden' it once I clicked off those cells onto a different blank cell? Or do those cells stay in an a boldened appearance?
+Mofo Jackson to unbold you would need to use = false I think what you are trying to do is bold the cells when they are selected and when something else selected the original range would unbold You would need to bold the selection and put the selection range in a cell. Then when the selection was changed you would need to unbold the range that was put in the cell and bold the new selection
So first, thanks for some teaching. I know I am late so not sure if you are still active. Second, I am trying to create a Form that will input data on a specific row. I know how to get the row address, but I am not sure how to get the VBA to select that row and add the remaining data. The code looks like: Range("L4").Select ActiveCell.Formula2R1C1 = _ "=CELL(""address"",INDEX('Data'!RC[-11]:R[30]C[-11],MATCH(RC[-6],'Data'!RC[-11]:R[30]C[-11],0)))" Now how do I use that information to select the row and complete the data? Maybe something like: Row = dataSheet.Range("A" & Range("L4").Select ActiveCell.Formula2R1C1 = _ "=CELL(""address"",INDEX('Data'!RC[-11]:R[30]C[-11],MATCH(RC[-6],'Data'!RC[-11]:R[30]C[-11],0)))").End(xlUp).Offset(1).Row I think I am over complicating it....
You can use the following. The first number is the row and the second is the column. After equals is just text of the cell address so replace with whatever you want Cell(1,1) = "a1" Cell(1,2) = "b1"
Hello all, new to Macros. I recorded a macro and it worked great on the spreadsheet I created it for. Issue I have is it will only work on spreadsheets with the same number of rows. But the number of rows vary on each spreadsheet I need to to use the macro for. Is there a simple solution to this issue?
@@EverydayVBAExcelTraining thanks for the reply. Do you man your videos on last line? Is there no shortcut to change range from A171 to end of range? ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _ "A2:A171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _ "B2:B171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _ "H2:H171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _ "N2:N171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("On Vessel (11)").Sort .SetRange Range("A1:O171") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("B1").Select ActiveCell.FormulaR1C1 = "Consignee/Dealer" Columns("C:C").Select
Hello Chris. I am just beginning to get into VBA, do you know of a way to keep 2 pivot tables the same amount of Rows apart? I have Pivot Tables that grow with each day and I want to keep 3 rows in-between them without having to manually do it?
+Greg Nelson good question. Do you use a macro to refresh the pivots. You would need to find the last row of the of the first pivot and then insert 3 rows. I have a video on the finding the end row. That should help
I don't currently use a macro to refresh the pivots, but I saw that you have a video on that. I may start doing that. The only issue with "inserting" rows is that when the month resets I need to remove all the extra rows. The Pivot is day by day so when it goes to a new month it will go from 28-31 days down to 1. The layout needs to remain the same so they will always have one pivot above another.
Okay that makes sense. You will probably need do insert rows and also delete rows if there is move than 3 rows. Another way do this is not delete the rows but hide them. This might be the simplest way to do this. You would have 30+ rows between the two pivots and if use a loop to hide the rows that you wanted to. It is kind of hard to provide more details without seeing the workbook.
EverydayVBA Thanks a lot for the response. Your videos are really helpful. Just one more question. How to write a code in which when the range changes, the value in the old range is deleted??Like if my range is cell D3, it's value is whatever function i have put, but when i change the range to D4 and run the macro again, the value of D4 equals that function and that function is deleted from D3.
Hey Chris, Great Video! I used the offset as in your video, works great. I'm using this in reports with variable numbers of rows. I can count the rows, and make my offset for the range i want, accordingly. How do I select this range in code when I have only the address for the first cell?
how to write in the cells put the column value through vba userform?. example A1=1001,A2=140,A3=ABCD, A4=MM/DD/YYYY B1=1002, same as above mentioned A2 A3 A4.............etc... can you explain as well add,update & delete button. I hope you.
Hello Genius, We need help.. We are manufacturing company, we created cartoon box stickers in #excel sheet, but we are facing one problems we are added box numbers also, we want to print box numbers continously when we are taking print continue box numbers also examples we take 100 stickers starting 1to 100 box numbers to print continuesly Can any one know how to do this in Excel using #macro or #VBA #manufacturing COMMENTS
So I recorded myself transposing rows to columns and back, but it only works when the start of the list is in cell A1. i have tried playing with "Selection.CurrentRegion" and other variables but I can't find the right one. my goal is using the top/left as a pivot point to transpose a list. Your videos are amazing and I've learned a LOT but I haven't seen you cover this specific topic yet, can you help? Here is one of the recording I created: Sub Transpose_Column_to_Row() ' ' Transpose_Column_to_Row Macro ' ' Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Range("B1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.ClearContents Range("A1").Select Selection.Delete Shift:=xlToLeft End Sub
Okay that makes sense. And that is one of the pains with using the Macro Recorder. It is very very literal. The way I would do it would be to replace the first Range("A1") with dim rng as range set rng = activecell.currentregion activecell.currentregion.Cells(1,1).select and Range("B1").select with activecell.currentregion.cells(1, rng.columns.count +1).select then next Range("A1") through clearcontents with rng.clearcontents the next Range("A1") rng.cells(1,1).select Give that a shot and let me know if you need anyhelp
Damn you are good! Thanks a lot! Unfortunately the next problem is creating one to set it back. I managed to apply what you provided me to the row to column code but it's shifting the cells over 1 space what am i missing? Sub Transpose_Row_to_Column() ' ' Transpose_Row_to_Column Macro ' ' Dim rng As Range Set rng = ActiveCell.CurrentRegion ActiveCell.CurrentRegion.Cells(1, 1).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy ActiveCell.CurrentRegion.Cells(1, rng.Rows.Count - 1).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True rng.ClearContents rng.Cells(1, 1).Select Selection.Delete Shift:=xlUp End Sub
I tried it but the problem is that it's outputting the results 1 cell to the left already, so when it deletes the cell whether it is up or to the left it doesn't effect the outcome. Any thoughts? Also I REALLY appreciate your help with all this.
Dear sir I asked you to solve my problem about how to get previous record and next record with the same coding and sheet that you used your video related to transfer the data from one sheet to another sheet...I sent the pictures on your Twitter and messanger...plz solve my problem....
Need urgent help with this: I need to have a formula that checks in sheet Matrics, Match Range("B14") with column A and if Value found: if yes, replace value from Ceelist.text to Matrics sheet, column B. If no, continue normally and add new Row below. See formula: Private Sub Butcee_Click() Dim Lastrow As Long, Rw As Range, Reg As String Reg = Range("B14").Value Lastrow = Worksheets("Matrics").Cells(Rows.Count, "A").End(xlUp).Row + 1 Ckrow = Worksheets("Matrics").Range("A3:A" & Lastrow).End(xlUp).Row If Ceelist.Value = "" Then GoTo Diss Else For Each Rw In Range("A" & Ckrow) If UCase(Rw) = Reg Then Cells(Rw.Row, 2) = Ceelist.Text Next Reg Exit Sub Worksheets("Matrics").Range("A" & Lastrow).Value = Range("B14").Value Worksheets("Matrics").Range("B" & Lastrow).Value = Ceelist.Text Worksheets("Matrics").Range("C" & Lastrow).Value = Worksheets("Matrics").Range("V22").Value MsgBox "Upload complete", vbOKOnly Ceelist.Value = "" Exit Sub Diss: MsgBox "No Comments Added", vbOKOnly End Sub
@@EverydayVBAExcelTraining LOL! AND 'POOF'! She was gone! Dude, really appreciate how responsive you are - you've responded to my questions in the past as well. Wishing you incredible success. Hmmm... your 'teaching style' is really good... I think VBA is just not so popular - more of a niche. Have you considered presenting another more.. 'mainstream' topic? One of these 'newer', 'sexier' scripting or programming languages? Python? Just a thought - best of luck!
This is awesome - just starting out on VBA and love your channel
Thanks
Have to say my friend, you are the best at explaining this stuff.. clear, intelligible and at a relaxed pace.
Thanks for the feedback
You are an excellent instructor, explaiining very well. Thank you
Thanks man, keep it up. You are good in explaining things while demonstrating. It helps me learn faster.
You're a damn good teacher for an old fart like me.
The last programming I did was around the turn of the millennium. (except for a few modifications to old, existing ones)😂
@@AschwandenJakob yeah I learned from a book haha
from a teaching perspective that was a great video. cheers
Thanks for the props
Best channel ever seen. I really enjoying your video and learning. Best of the best brother..👍🏆
Thank you Sir
Thank you so much EverydayVBA very usefull tutorial. God bless you more. Best regards from Nicaragua.
Gracias. Best of luck on your VBA journey
best VBA channel on YT. and I've seen a few.
Thanks for the Props
Very well explained, i need to use this kind of selection to select some random data generated and delete it, then make the program in question(a simple accumulator) just start over on the cell it started. See i have developed a code but it just deletes the data and then keeps going on the next row instead of going back to the 1st row it started.
AC = ActiveCell.Value
ActiveCell.Offset(1, 0).Value = AC
Range("E3:E10").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, transpose:=True
Rows("4:9").Select
ActiveCell.Offset(1, 0).Value = AC
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("E3").Select
ActiveCell.Offset(1, 0).Value = AC
End Sub
excelent you save my macro
Glad it helped
I have a workbook with 3 sheets. This workbook pulls dynamic data from a network, so while I can sometimes know what column I'm pulling from, I never know what row I'm on. Excel won't let me use the following line of code:
Sheet3.Range(Cells(report_row, 2), Cells(report_row, 3)).Select
I've also tried this without the Range method (just cells), but nope.
Have you every used ActiveCell. That will always be the cell that is selected.
These will return the row you are on
ActiveCell.row
Selection.row
@@EverydayVBAExcelTraining Indexing the row wasn't the problem. It was getting range to play nice with variables. I ended up using this line of code:
Set rng = Sheet3.Range("B" & report_row, "F" & report_row)
Thankfully that's all I need it to do for now, but I have no idea how to get it to use my column variable.
You could add .cells to the range.
Since your range has 1 row it would look something like this
Range(...).cells(1,2)
That would be the second column in the range
can you correct this code, if wrong
Cells(Sheet4.Rows.Count, "B").End(x1UP).Offset(1, 0).Select
Hi i would like to ask. I have here pivot table and i would like to select the cell beside the certain cell with specific value. How do i do that?
Love your VBA channel, just getting started on VBA. Was wondering how one can reference rows dynamically when wanting to hide rows? Since the design of the sheet is not complete I am inserting more rows and adding more items but when I write my code:
eg.
'*************************************************
'** List of new Equipment Section
'** Amplifier
'** Reveals or hides row 56
'** Amplifier Reveals line: 56
'*****************************************************
'** Room Technical Mods Section
'** 1. There is an Amp, we show line 56 and lines 90-91 but hiding line 89
'** 2. There is no Amp, we hide lines 56 and lines 90-91 but show line 89
'** 3. Shows all lines for review - nothing hidden
'*****************************************************
If Not Application.Intersect(Range("N11"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Amplifier": Rows("56").EntireRow.Hidden = False
Rows("89").EntireRow.Hidden = True
Rows("90:91").EntireRow.Hidden = False
Case Is = "No Amplifier": Rows("89").EntireRow.Hidden = False
Rows("56").EntireRow.Hidden = True
Rows("90:91").EntireRow.Hidden = True
Case Is = "Reveal All Amp": Rows("56").EntireRow.Hidden = False
Rows("89:91").EntireRow.Hidden = False
End Select
End If
If I have to insert a new line before line 56 or even after 56 but before 89-91 then my references which to me are a direct reference no longer work on the correct rows unless I change the numbers appropriately. Is there a way to dynamically assign a name to row and reference it that way in my formulas above? Note: this code works (after lots of research) but it is not in a module, it is a worksheet macro. I do this because I want to be able to select (based on drop down list which rows are shown and not shown for what I am designing. Many thanks to anyone who can help.
You might need to use a Match formula or maybe subtotal? Not sure that helps
i have a sheet but date is changed every day in this sheet i make filter i select below data except first row and delete how can i make it in VBA know that data is changed every day
hello I don't know if you can solve this...
I'm in a table with some cells hidden because a filter that left cells e25 and e13 visible (well the entire rows 25 and 23 visible)...
if I'm moving up with the key arrows from cell e25 to e13 with a single keystroke I'm in e13... the upper arrow doesn't take me step by step to e24, e23, etc to reach e13... because they are hiden
however... if I'm using a vba code
range("e25").select
selection.offset(-1.0).select
it doesn't take me to e13 but e24
if I record a macro using the arrows the code is:
activecell.offset(-12,0).range("a1").select
how can I move by visible cells with a code?
Copying ranges works by making them equal to each other
Range("e25") = range("e13")
That may not help your specific needs but will get you on the way
I am going through a sheet where each employee has several rows of amounts. I want to copy the cells paste special "transpose" then delete the rows below the first of that employee. I have the first part but when it goes to delete rows. I doesn't move down below the line i want to keep.
Can you post your code?
@@EverydayVBAExcelTraining AC = ActiveCell.Value
ActiveCell.Offset(1, 0).Value = AC
Range("E3:E10").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, transpose:=True
Rows("4:9").Select
ActiveCell.Offset(1, 0).Value = AC
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("E3").Select
ActiveCell.Offset(1, 0).Value = AC
how i can select multiple ranges from two sheets? please explain. Thanks
The easiest way to record a macro and then alter the code
Hi Chris I am just getting into VBA and Macros etc and I am glad I another excellent instructor. (I have a few of you who's videos I love and prefer among so many I have searched for, and you're in that list) So at the very end with the:
Selection.Font.Bold = True
Would that 'unbolden' it once I clicked off those cells onto a different blank cell? Or do those cells stay in an a boldened appearance?
+Mofo Jackson to unbold you would need to use = false
I think what you are trying to do is bold the cells when they are selected and when something else selected the original range would unbold
You would need to bold the selection and put the selection range in a cell. Then when the selection was changed you would need to unbold the range that was put in the cell and bold the new selection
Great video :) Still one question: How can I refer to a cell on another sheet? Normally this can be done by =Sheet2!B6, but how can you do it in VBA?
Great question.
The syntax is
Sheets("Sheet2"). Range("B6")
So first, thanks for some teaching. I know I am late so not sure if you are still active.
Second, I am trying to create a Form that will input data on a specific row. I know how to get the row address, but I am not sure how to get the VBA to select that row and add the remaining data.
The code looks like:
Range("L4").Select
ActiveCell.Formula2R1C1 = _
"=CELL(""address"",INDEX('Data'!RC[-11]:R[30]C[-11],MATCH(RC[-6],'Data'!RC[-11]:R[30]C[-11],0)))"
Now how do I use that information to select the row and complete the data?
Maybe something like:
Row = dataSheet.Range("A" & Range("L4").Select
ActiveCell.Formula2R1C1 = _
"=CELL(""address"",INDEX('Data'!RC[-11]:R[30]C[-11],MATCH(RC[-6],'Data'!RC[-11]:R[30]C[-11],0)))").End(xlUp).Offset(1).Row
I think I am over complicating it....
You can use the following. The first number is the row and the second is the column. After equals is just text of the cell address so replace with whatever you want
Cell(1,1) = "a1"
Cell(1,2) = "b1"
Hello all, new to Macros. I recorded a macro and it worked great on the spreadsheet I created it for. Issue I have is it will only work on spreadsheets with the same number of rows. But the number of rows vary on each spreadsheet I need to to use the macro for. Is there a simple solution to this issue?
Yeah that can be a pain. Look for my video on the last line. That should help
@@EverydayVBAExcelTraining thanks for the reply. Do you man your videos on last line?
Is there no shortcut to change range from A171 to end of range?
ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _
"A2:A171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _
"B2:B171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _
"H2:H171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("On Vessel (11)").Sort.SortFields.Add2 Key:=Range( _
"N2:N171"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("On Vessel (11)").Sort
.SetRange Range("A1:O171")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B1").Select
ActiveCell.FormulaR1C1 = "Consignee/Dealer"
Columns("C:C").Select
selection.Insert shift:=x1Toright getting an error on this line
Is that because you have x[one] instead of xLright
Hello Chris. I am just beginning to get into VBA, do you know of a way to keep 2 pivot tables the same amount of Rows apart? I have Pivot Tables that grow with each day and I want to keep 3 rows in-between them without having to manually do it?
+Greg Nelson good question. Do you use a macro to refresh the pivots.
You would need to find the last row of the of the first pivot and then insert 3 rows.
I have a video on the finding the end row. That should help
I don't currently use a macro to refresh the pivots, but I saw that you have a video on that. I may start doing that. The only issue with "inserting" rows is that when the month resets I need to remove all the extra rows. The Pivot is day by day so when it goes to a new month it will go from 28-31 days down to 1. The layout needs to remain the same so they will always have one pivot above another.
Okay that makes sense. You will probably need do insert rows and also delete rows if there is move than 3 rows.
Another way do this is not delete the rows but hide them. This might be the simplest way to do this. You would have 30+ rows between the two pivots and if use a loop to hide the rows that you wanted to. It is kind of hard to provide more details without seeing the workbook.
How to make a function the range of the cell??That is, the range is variable and changes depending upon the values in the function.
I have a video on making functions.
You can also make use the built in excel funcruons
EverydayVBA Thanks a lot for the response. Your videos are really helpful. Just one more question. How to write a code in which when the range changes, the value in the old range is deleted??Like if my range is cell D3, it's value is whatever function i have put, but when i change the range to D4 and run the macro again, the value of D4 equals that function and that function is deleted from D3.
I really like to use currentrange
EverydayVBA Hello Sir. I am an excel beginner and it would be great if you could tell me how to go about the code.
Sir I am beginner. how can i use if (if else, end if) else (if else) end if statement with active cell ?
In code you just insert a string in the cell
Active cell = "if(...)"
EverydayVBA plz give me your responding mail , I want to briefly discuss with you. need your help
Dear Sir, How do I select the long range exceeding Z i.e A1:AB45
Range("A1:AB45"). Select
Hey Chris, Great Video! I used the offset as in your video, works great. I'm using this in reports with variable numbers of rows. I can count the rows, and make my offset for the range i want, accordingly. How do I select this range in code when I have only the address for the first cell?
Will you ever consider making VSTO tutorial videos?
how to write in the cells put the column value through vba userform?. example A1=1001,A2=140,A3=ABCD, A4=MM/DD/YYYY
B1=1002, same as above mentioned A2 A3 A4.............etc...
can you explain as well add,update & delete button. I hope you.
How do you select the last cell with data in a range (say b8:i10)?
Hello Genius,
We need help..
We are manufacturing company, we created cartoon box stickers in #excel sheet, but we are facing one problems we are added box numbers also, we want to print box numbers continously when we are taking print continue box numbers also examples we take 100 stickers starting 1to 100 box numbers to print continuesly
Can any one know how to do this in Excel using #macro or #VBA #manufacturing COMMENTS
Do you have a loop that prints the stickers?
Is there only one Excel spreadsheet in the process?
Paate your code and I can take a look
Nice! Now show us how to do something similar in Google Sheets using Script !!
So I recorded myself transposing rows to columns and back, but it only works when the start of the list is in cell A1. i have tried playing with "Selection.CurrentRegion" and other variables but I can't find the right one. my goal is using the top/left as a pivot point to transpose a list. Your videos are amazing and I've learned a LOT but I haven't seen you cover this specific topic yet, can you help?
Here is one of the recording I created:
Sub Transpose_Column_to_Row()
'
' Transpose_Column_to_Row Macro
'
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
Selection.Delete Shift:=xlToLeft
End Sub
Okay that makes sense. And that is one of the pains with using the Macro Recorder. It is very very literal.
The way I would do it would be to replace the first Range("A1") with
dim rng as range
set rng = activecell.currentregion
activecell.currentregion.Cells(1,1).select
and Range("B1").select with
activecell.currentregion.cells(1, rng.columns.count +1).select
then next Range("A1") through clearcontents with
rng.clearcontents
the next Range("A1")
rng.cells(1,1).select
Give that a shot and let me know if you need anyhelp
Damn you are good! Thanks a lot! Unfortunately the next problem is creating one to set it back. I managed to apply what you provided me to the row to column code but it's shifting the cells over 1 space what am i missing?
Sub Transpose_Row_to_Column()
'
' Transpose_Row_to_Column Macro
'
'
Dim rng As Range
Set rng = ActiveCell.CurrentRegion
ActiveCell.CurrentRegion.Cells(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.CurrentRegion.Cells(1, rng.Rows.Count - 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
rng.ClearContents
rng.Cells(1, 1).Select
Selection.Delete Shift:=xlUp
End Sub
+xMISTERxJ last line should be xltoleft I think
I tried it but the problem is that it's outputting the results 1 cell to the left already, so when it deletes the cell whether it is up or to the left it doesn't effect the outcome. Any thoughts? Also I REALLY appreciate your help with all this.
+xMISTERxJ okay my bad. Where it says columns.count - 1
Change it to columns.count + 1
So how can i select range("a1") to range("b1").End(xldown)
Thank you!
Do you want to select a1 to b1?
Record a macro and it will write the code for you
I mean i select a1 to end of colum b, and (end of colum b can be b1 or b2,.....)
I did try with macro but a can't
Dear sir I asked you to solve my problem about how to get previous record and next record with the same coding and sheet that you used your video related to transfer the data from one sheet to another sheet...I sent the pictures on your Twitter and messanger...plz solve my problem....
Hello!! I'm Marcos from Brazil, i'd like to know is possible to broken captcha with excel vba? thanks
+marcos roberto to break a captcha would be really challenging and there are probably better options like browser extensions
Need urgent help with this:
I need to have a formula that checks in sheet Matrics, Match Range("B14") with column A and if Value found:
if yes, replace value from Ceelist.text to Matrics sheet, column B.
If no, continue normally and add new Row below.
See formula:
Private Sub Butcee_Click()
Dim Lastrow As Long, Rw As Range, Reg As String
Reg = Range("B14").Value
Lastrow = Worksheets("Matrics").Cells(Rows.Count, "A").End(xlUp).Row + 1
Ckrow = Worksheets("Matrics").Range("A3:A" & Lastrow).End(xlUp).Row
If Ceelist.Value = "" Then GoTo Diss Else
For Each Rw In Range("A" & Ckrow)
If UCase(Rw) = Reg Then Cells(Rw.Row, 2) = Ceelist.Text
Next Reg
Exit Sub
Worksheets("Matrics").Range("A" & Lastrow).Value = Range("B14").Value
Worksheets("Matrics").Range("B" & Lastrow).Value = Ceelist.Text
Worksheets("Matrics").Range("C" & Lastrow).Value = Worksheets("Matrics").Range("V22").Value
MsgBox "Upload complete", vbOKOnly
Ceelist.Value = ""
Exit Sub
Diss:
MsgBox "No Comments Added", vbOKOnly
End Sub
I think you would need an If then else because you are exiting the sub.
That would need to be within the if statement
Hi! Are you still active for a question?
Hey what's up
@@EverydayVBAExcelTraining LOL! AND 'POOF'! She was gone!
Dude, really appreciate how responsive you are - you've responded to my questions in the past as well. Wishing you incredible success. Hmmm... your 'teaching style' is really good... I think VBA is just not so popular - more of a niche. Have you considered presenting another more.. 'mainstream' topic? One of these 'newer', 'sexier' scripting or programming languages? Python? Just a thought - best of luck!
Thanks for the props. My next topic will be on Knime. Which is a a data manipulation tool. It is pretty awesome. It is like Alteryx but open spurce