I have watched 8 videos now and still surprised by the clear way you explain VBA. The way others explain (by video or books) is often so much complicated after a few lessons, that it sometimes forces you to quit because there is no fun at that moment. But your videos are really fun. Hopefully it will all come together at the end of this serie. Thank you!
Dear Dan, I tried to learn VBA code from books/some excel help sites some time ago even though I am quite profficient on formulas, left it undone.. I was waiting for you to take excel tutorials to the next level ! thank you and bless you as well.
Watching this in may-2017 i really appreciate what you did in this great course , i think this is the best tutorial for vb on youtube .. so thank you bro
at the moment I'm reviewing your tutorials to finish this : have a long list (i.e.25 clolumns long, 14.000 rows) of expenses with each personel's name in one column. I want to copy each personnels expense amount and name to different sheets and name the sheets with each personnel's name. in those sheets there are formulas which calculate a score for each personnel in a seperate table. last consolidate these scores for each personnel to a new sheet, (i.e. personnel name and score in two columns.
Really appreciate you taking the time to record and upload these. I've found the videos great to watch and very informative. If i was to suggest one area for improvement though, it would be a better name structure for your downloadable files. I couldn't find the one for this so i'm only watching and not doing. Thanks again and keep up the good work! :)
I used the following formulas for the last row, last column and rows and columns after them. The rows or columns after them can be used in a Loop to recalculate. I do have a question about sorting data from least to greatest or A to Z as well as offset. I am not sure how to do these function. I appreciate the videos. I learned a lot from them. lastrow =cells(rows.count, 1).end (xlup).row lastcol = Cells(1, Columns.Count).End(xlToLeft).Column lastrow+1 lastcol+1
I don't know why when I used the codes from excelvbaisfun and other youtube channels it failed to work but your codes worked hahaha weird.....glad you commented though :D
good morning, first want to express gratitude for the time and efforts of teaching people your skills. i was wondering if there is a way you can show how to delete lastrow and proceeding rows with a dynamic range. i have a report that has the same format but everyday the report changes with how many rows. thank you in advance. lisa mendez
Thank you Amr Kay, that's exactly what my goal is! More vids coming this weekend. Also thinking of doing guitar/piano/singing and music theory lessons on youtube. God bless! Dan
Thanks for these videos it is a great review for me. I do have a question - I entered the code in exactly as you have it here, but it fails on the "TheWorkbook." part of it. If I remove that it works, but I am wondering why it doesn't work.
I have put in the line like you show ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row but I'm getting an error that the "Object does not support this property or Method. Please help I'm a newbee and it is most likely me..Thanks
Dear Charles, did you have this statement going into a variable or something. If you put ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row on a line of code, it doesn't know where to go, so you have to trap the number into a variable. So try this: charlesNum = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row This way the number will be trapped in the variable charlesNum, and when debugging, you can see that charlesNum has a number within it. Best, Dan
Really appreciate your videos, it is a great tool for me, as a beginner, to learn how to use vba. However, i have some problems in 4:52, i could not type in the symbols you typed, is it the & ?
Yes, Yau Shun Ming. Exactly. The Ampersand (&) symbol allows you to join 2 or more things together. Example: myWord = "Blow" & "fish" so myWord would take on the value of "Blowfish" OR Range("A1") = "The exact time is " & Time Which at this moment would pop the following into cell A1 "The exact time is 1:17 PM"
Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan
Just incase anyone is interested. I reasently came across a situation where I needed to find the last row in a worksheet that had formulas Like=IF(B2="","",B2+C2) this was copyed down to row 500. The method shown here resulted in the last row as row 500 So I did some reasurch for a alterntive method and found this code.. With myWs Set FOUNDRANGE = .Columns("A:A").Find("*", After:=.Range("A1"), searchdirection:=xlPrevious, LookIn:=xlValues) If Not FOUNDRANGE Is Nothing Then MyWsLr = FOUNDRANGE.Row End With If MyWsLr = 1 Then MyWsLr = 2 End If (myWs is the variable set for the worksheet) (FOUNDRANGE is also a variable) (MyWsLr is the variable set for the last row on this worksheet) This should find the last row in column A in the worksheet excluding any formulas.
when I try to run InputDataVariable = txtInputText Range("B2").End(xlDown).Offset(1, 0).Select ActiveCell.Value = InputDataVariable I get this error "Run-time error '1004': Application-defined or Object-defined error" Now if I have cell "B2" has content in it, then the instruction seems to work just fine, but the instruction will not populate the first row... Any Suggestions of Comments???
Hi Dan, I don't know if you'll read this message since this video is kind of old, but I had a question: I copied in the exact same code as you to find the last row ( ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row ), and for some reason I keep getting an error message that says "Run-time error '438': Object doesn't support this property or method" Is this because I could be using a different version of Microsoft Excel, or is it because I've entered something wrong into the code? Thanks for all of your help, by the way! Your videos are wonderful and so easy to follow.
Hi Alice, The information (what row is the last) has nowhere to go, so you must plop it somewhere, like a cell or a variable. Something has to receive the value. Copy this Alice last Row = thisworkbook.sheets ("myreport").cells (rows.count,1).row 'Make sure you have a sheet called myreport if you're telling excel that's where it's coming from as in above. Thanks Dan
Hi, I used it like LastRow = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row but I get the exact same error message and I dont even know where to start debugging it. Thanks for all of your help!
Sub MacroRow() LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row End Sub -this worked for me I changed Row.Count to Rows.Count
Sounds like fun. Keep watching and if you need help, send me a workbook with dummy data or real if you like, and maybe I can explain while making a video to help the vba community. Dan
My first line is lastrow=Book1.sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row I kept getting the Run-time error '424'. What went wrong with my code?
Veronica Huang probably get rid of Book1 and replace it with ThisWorkbook So either lastrow=ThisWorkbook.sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row OR lastrow=Workbooks("Book1").sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row Thanks! Dan
Hi Dan, first Thanks for the videos, such a good help. I have a little trouble when it comes to writing the code ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row It seems its alright until the: ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1). But when i write End it shows ROW METHOD OF RANGE CLASS FAILED. (When I debug it shows the . between the .....Count, 1).(End(xl......... highlighted. Any ideas please?
Dan!! What if you wanted to have a way to sum at the bottom of a column. I have a report that changes length every month, and I want to be able to get the sum. I feel like I am so close. Here is my code. lastrow = ThisWorkbook.Sheets("TAG DYN").Cells(Rows.Count, 25).End(xlUp).Row ThisWorkbook.Sheets("TAG DYN").range ("Y2:Y"" & lastrow).sum The sum at the end is wrong, but is there a way I could get this to work? If you could just put the code that would sum your column B in this video
Hey Dan, thank you for these videos. I have a question regarding finding the first column of a certain row. I thought of something like this: Firstcol = ThisWorkbook.Sheets("My report").Cells(13, Columns.Count).End(xlToRight).Column , the problem of this is that it gives me back Firstcol=9, when actually the first written column of row 13 is number 4. Could you please help me?
What is the advantage of ".count" and "End(xlUp)" over: x = ThisWorkbook.Sheets(1).Cells.SpecialCells(xlLastCell).Row ^ gives me the last row regardless of a column.
Dear Dan, Thank you for your tutorial from which I learn a lot as a beginner in Excel VBA. I am stuck in a project where I generate reports for different weeks and each report is different is size. I am trying to generate a code that would copy whatever data I have starting always at B9 (same sheet, let's say sheet name is "Weekly Report") then copy and paste values with source formatting to another sheet ( named "General Report" ) at B9 also then clear the selected data in "weekly report" But when I generate another report for a different week in the Weekly report sheet and I run the code, the newly generated report will be copied this time to the general report sheet starting also in column B but ONE Row below the first one and so forth. All report are from column B to Column I but with variable number of rows. Please help me. Thanks Nabil Mourad
Hi Daniel. I've been playing around with using this method in a macro that uses option explicit. In the case of this tutorial, if you were in option explicit what would you dim your variable Lastrow as?
Hi Dan. Thanks for the video! Would like to ask you a question a little unrelated. I want to create a code that deletes every page in my workbook except for one called "Paramaters" for example. How can i write this? Thanks
Hi Gerald, Try something like this: dim ws as worksheet for each ws in thisworkbook.sheets if ws.name "Paramaters" then ws.delete end if next ws I would highly advise debugging this slowly the first time with F8 key to ensure things are spelled correctly etc. If that doesn't work, please reply or contact me on my contact form on excelvbaisfun.com Thanks Dan
lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row this is what I got... and I keyed something in A1 to A10 just to test this... but lastrow kept = 1.... what did I do wrong?
This is a great video. by the way, do you know what the letters "xl" in xlup, xldown, (etc.) mean? I mean obviously they sound like "excel" but I see no reason the programmers of VBA would make that necessary.
How do other macros in the workbook affect creating new macros if they're not being used? I'm now worried about having to account for how many macros i'm making and what's in them o_o I also tried to copy the code presented and ended up with the 1004 error
ThisWorkbook.Sheets("My Report").cells(rows.Count,1).end(xlup).row. Doesn't like the "row" at the end. I can do "select" and that works. Running Excel 2016.
lastrow = ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row When putting above code in I get subscript error out of range runtime error 9. Even though my sheet is named My Report....
optionsupdate Just collapse it and drag and drop the boundaries. There's 3 buttons in windows applications. leftmost is minimize, middle is expand/collapse, right is red X to close application. Use middle one to collapse and then resize as needed.
Roshni Lobo Hi Roshni. I think the guy in the back was from www.incredimail.com - a free email service. But unfortunately he doesn't help with office. Yours, Dan
can somebody please help! I'm writing the code exactly how it is presented but when i'm writing LastRow = ThisWorkbook.Sheets("MyReport").Cells(Rows.Count, 1).End(xlUp).Row i keep getting the Run-time error '1004' Application defined or object defined error has anybody ran into the same problem?
what about this structure: lastcol = ThisWorkbook.Sheets("My Report").Cells(1, Columns.Count).End(xlToLeft).Column it works on the same principle; to find the number of the last column without referring to selection Good luck :)
ExcelVbaIsFun Sub drugsort1() ' ' drugsort1 Macro ' ' Range("O52:R71").Select Selection.Copy Sheets("Ennogen Goods in").Select Range("B2660").Select Range("b99999").End(xlUp).Select ActiveCell(2).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub this is the code, it takes a list from one sheet to another sheet, but I want info posted at first empty row, but xlup seems to make it stop in 5 -10 rows before empty cells, each time I use this the cell gets pasted lower down leaving bigger empty gap. Yours gratefully.
lastrow = thisworkbook.Sheets("My Report").Cell(row.Count, 1).End(xlUp).row im getting a object required error 424, how do i fix this, the full code is : Sub hjj() lastrow = thisworkbook.Sheets("My Report").Cell(row.Count, 1).End(xlUp).row End Sub
What other code is in that macro? Or in that workbook? If you don't have room enough to paste it here, post this on our facebook page. You can also put screenshots and even upload the wb on there. Link to that is in the description. We'll help you through it! Dan
Hi Jayjay F, lastrow is a variable and thus it changes values. We don't want the word lastrow as part of the range, it might look like this to Excel once it's done calculating: range(a2:clastrow), instead of a2:c20 or whatever value is currently found in the variable lastrow. Thanks Dan
ExcelVbaIsFun is it because one is a string and the other is a variable, so range("a1:c20") inside brackets is a string so variable has to be out side quotation marks?
ExcelVbaIsFun is it because one is a string and the other is a variable, so range("a1:c20") inside brackets is a string so variable has to be out side quotation marks?
I have watched 8 videos now and still surprised by the clear way you explain VBA. The way others explain (by video or books) is often so much complicated after a few lessons, that it sometimes forces you to quit because there is no fun at that moment. But your videos are really fun. Hopefully it will all come together at the end of this serie.
Thank you!
Dear Dan, I tried to learn VBA code from books/some excel help sites some time ago even though I am quite profficient on formulas, left it undone.. I was waiting for you to take excel tutorials to the next level ! thank you and bless you as well.
Watching this in may-2017
i really appreciate what you did in this great course , i think this is the best tutorial for vb on youtube .. so thank you bro
Thanks for your amazing teaching technique, I'm new to VBA and your videos make any learner really comfortable.
The best online VBA tutorial!!!
at the moment I'm reviewing your tutorials to finish this :
have a long list (i.e.25 clolumns long, 14.000 rows) of expenses with each personel's name in one column.
I want to copy each personnels expense amount and name to different sheets and name the sheets with each personnel's name.
in those sheets there are formulas which calculate a score for each personnel in a seperate table.
last consolidate these scores for each personnel to a new sheet, (i.e. personnel name and score in two columns.
Watching this in 2021 and getting value from it. Thank you !!
Glad it was helpful!
Really appreciate you taking the time to record and upload these. I've found the videos great to watch and very informative. If i was to suggest one area for improvement though, it would be a better name structure for your downloadable files. I couldn't find the one for this so i'm only watching and not doing. Thanks again and keep up the good work! :)
Spencer, You're absolutely right. I'll have to take a weekend and get them organized. Thanks, DAn
Thank you! Great video!
I used the following formulas for the last row, last column and rows and columns after them. The rows or columns after them can be used in a Loop to recalculate. I do have a question about sorting data from least to greatest or A to Z as well as offset. I am not sure how to do these function. I appreciate the videos. I learned a lot from them.
lastrow =cells(rows.count, 1).end (xlup).row
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
lastrow+1
lastcol+1
I don't know why when I used the codes from excelvbaisfun and other youtube channels it failed to work but your codes worked hahaha weird.....glad you commented though :D
Love this! Thankyou. You're making is so (!) clear.
Good Work Bro.. It is really helping me with Excel...
good morning,
first want to express gratitude for the time and efforts of teaching people your skills. i was wondering if there is a way you can show how to delete lastrow and proceeding rows with a dynamic range.
i have a report that has the same format but everyday the report changes with how many rows.
thank you in advance.
lisa mendez
Thank you Amr Kay, that's exactly what my goal is! More vids coming this weekend. Also thinking of doing guitar/piano/singing and music theory lessons on youtube. God bless! Dan
Thank you for making such educational videos!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You are welcome! Thanks for enjoying them!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Really good series of videos. Thank you!!
Good one
I keep having this error: Run-Time Error 9 : Subscript out of range, Could you please tell me how I can fix my code? Thanks so much
I have to watch this again 3 years later.
gotta deal with a new excel project and obviously , python is not as easy as vba when handle cvs data.
Good luck, MrGrape on your new project!
Thanks
Dan
ExcelVbaIsFun Thank you
Thanks for these videos it is a great review for me. I do have a question - I entered the code in exactly as you have it here, but it fails on the "TheWorkbook." part of it. If I remove that it works, but I am wondering why it doesn't work.
+William Clark I think it's supposed to be 'thisworkbook', not 'theworkbook'
Great to hear aborucu! Lemme know if there are any topics or projects you're working on that could use a hand. I'll do my best to help. Dan
I have put in the line like you show ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row but I'm getting an error that the "Object does not support this property or Method. Please help I'm a newbee and it is most likely me..Thanks
Dear Charles, did you have this statement going into a variable or something. If you put
ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
on a line of code, it doesn't know where to go, so you have to trap the number into a variable. So try this:
charlesNum = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
This way the number will be trapped in the variable charlesNum, and when debugging, you can see that charlesNum has a number within it. Best,
Dan
Thank you for sharing wonderful videos. I have a question, if I would like to go to the next empty cell, what code I should enter??
Really appreciate your videos, it is a great tool for me, as a beginner, to learn how to use vba.
However, i have some problems in 4:52, i could not type in the symbols you typed, is it the & ?
Yes, Yau Shun Ming. Exactly. The Ampersand (&) symbol allows you to join 2 or more things together.
Example:
myWord = "Blow" & "fish"
so myWord would take on the value of "Blowfish"
OR
Range("A1") = "The exact time is " & Time
Which at this moment would pop the following into cell A1
"The exact time is 1:17 PM"
ExcelVbaIsFun thanks a lot!
Very welcome!
Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan
Amen
Hey can you provide me with a snippet to fetch data from Unix box putty and place in the excel sheet directly using VBA...Plzz if could help me!!!!
Just incase anyone is interested.
I reasently came across a situation where I needed to find the last row in a worksheet that had formulas
Like=IF(B2="","",B2+C2) this was copyed down to row 500. The method shown here resulted in the last row as row 500
So I did some reasurch for a alterntive method and found this code..
With myWs
Set FOUNDRANGE = .Columns("A:A").Find("*", After:=.Range("A1"), searchdirection:=xlPrevious, LookIn:=xlValues)
If Not FOUNDRANGE Is Nothing Then MyWsLr = FOUNDRANGE.Row
End With
If MyWsLr = 1 Then
MyWsLr = 2
End If
(myWs is the variable set for the worksheet)
(FOUNDRANGE is also a variable)
(MyWsLr is the variable set for the last row on this worksheet)
This should find the last row in column A in the worksheet excluding any formulas.
when I try to run
InputDataVariable = txtInputText
Range("B2").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = InputDataVariable
I get this error
"Run-time error '1004':
Application-defined or Object-defined error"
Now if I have cell "B2" has content in it, then the instruction seems to work just fine, but the instruction will not populate the first row...
Any Suggestions of Comments???
Hi Dan, I don't know if you'll read this message since this video is kind of old, but I had a question: I copied in the exact same code as you to find the last row ( ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row ), and for some reason I keep getting an error message that says "Run-time error '438': Object doesn't support this property or method"
Is this because I could be using a different version of Microsoft Excel, or is it because I've entered something wrong into the code?
Thanks for all of your help, by the way! Your videos are wonderful and so easy to follow.
Hi Alice,
The information (what row is the last) has nowhere to go, so you must plop it somewhere, like a cell or a variable. Something has to receive the value. Copy this
Alice last Row = thisworkbook.sheets ("myreport").cells (rows.count,1).row
'Make sure you have a sheet called myreport if you're telling excel that's where it's coming from as in above.
Thanks
Dan
Hi, I used it like LastRow = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row but I get the exact same error message and I dont even know where to start debugging it. Thanks for all of your help!
Sub MacroRow()
LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
End Sub -this worked for me I changed Row.Count to Rows.Count
Still getting message as Alice is getting. I have all the statements you mentioned
Everyone here was already using rows.count
Why is it that I use this function and it doesn't go to the next available row??
Sounds like fun. Keep watching and if you need help, send me a workbook with dummy data or real if you like, and maybe I can explain while making a video to help the vba community. Dan
My first line is lastrow=Book1.sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row
I kept getting the Run-time error '424'. What went wrong with my code?
Veronica Huang probably get rid of Book1 and replace it with ThisWorkbook
So either
lastrow=ThisWorkbook.sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row
OR
lastrow=Workbooks("Book1").sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Row
Thanks!
Dan
Thanks for the video!
That's a good news, I'll watch your guitar lessons for sure :).
Hi Dan, first Thanks for the videos, such a good help.
I have a little trouble when it comes to writing the code
ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row
It seems its alright until the: ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).
But when i write End it shows
ROW METHOD OF RANGE CLASS FAILED. (When I debug it shows the . between the .....Count, 1).(End(xl......... highlighted. Any ideas please?
Very welcome rialph!
Dan!! What if you wanted to have a way to sum at the bottom of a column. I have a report that changes length every month, and I want to be able to get the sum. I feel like I am so close. Here is my code.
lastrow = ThisWorkbook.Sheets("TAG DYN").Cells(Rows.Count, 25).End(xlUp).Row
ThisWorkbook.Sheets("TAG DYN").range ("Y2:Y"" & lastrow).sum
The sum at the end is wrong, but is there a way I could get this to work? If you could just put the code that would sum your column B in this video
Really great stuff!
Hey Dan, thank you for these videos. I have a question regarding finding the first column of a certain row. I thought of something like this: Firstcol = ThisWorkbook.Sheets("My report").Cells(13, Columns.Count).End(xlToRight).Column , the problem of this is that it gives me back Firstcol=9, when actually the first written column of row 13 is number 4. Could you please help me?
can you show me? excelvbaisfun@gmail.com thanks!
What is the advantage of ".count" and "End(xlUp)" over:
x = ThisWorkbook.Sheets(1).Cells.SpecialCells(xlLastCell).Row
^ gives me the last row regardless of a column.
awesome and really helpful
Dear Dan, Thank you for your tutorial from which I learn a lot as a beginner in Excel VBA.
I am stuck in a project where I generate reports for different weeks and each report is different is size. I am trying to generate a code that would copy whatever data I have starting always at B9 (same sheet, let's say sheet name is "Weekly Report") then copy and paste values with source formatting to another sheet ( named "General Report" ) at B9 also then clear the selected data in "weekly report" But when I generate another report for a different week in the Weekly report sheet and I run the code, the newly generated report will be copied this time to the general report sheet starting also in column B but ONE Row below the first one and so forth.
All report are from column B to Column I but with variable number of rows.
Please help me.
Thanks
Nabil Mourad
thanks for introducing the shortcuts:)
Very welcome!!
Hi Daniel. I've been playing around with using this method in a macro that uses option explicit. In the case of this tutorial, if you were in option explicit what would you dim your variable Lastrow as?
I appreciate so much your videos. parabens!!!
Thank you! I appreciate your kind words!!
-Dan
Thanks, Jan. It will come together, just experiment with the downloadable files. They're available in my dropbox video which has a link.
Starting a new channel for Piano Lessons, learn by Chord and play by ear! Check it out here: How to Play Piano by Chord - Beginning Chords - C
Hi Dan. Thanks for the video! Would like to ask you a question a little unrelated. I want to create a code that deletes every page in my workbook except for one called "Paramaters" for example. How can i write this? Thanks
Hi Gerald,
Try something like this:
dim ws as worksheet
for each ws in thisworkbook.sheets
if ws.name "Paramaters" then
ws.delete
end if
next ws
I would highly advise debugging this slowly the first time with F8 key to ensure things are spelled correctly etc.
If that doesn't work, please reply or contact me on my contact form on excelvbaisfun.com
Thanks
Dan
Many Thanks! Excelent videos BTW
how do u get it to ignore formatting?
Can you just use eof property instead of this long statement?
many thanks for sharing these wonderful videos....
kindlly share these sheets also
lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
this is what I got... and I keyed something in A1 to A10 just to test this... but lastrow kept = 1.... what did I do wrong?
This is a great video. by the way, do you know what the letters "xl" in xlup, xldown, (etc.) mean? I mean obviously they sound like "excel" but I see no reason the programmers of VBA would make that necessary.
That's great to hear Ram, glad that helps. Let me know if you have any questions about the content or projects you're working on. Dan
why the last column shows always Empty?
lastrow = ThisWorkbook.Sheets("myreport").Cells(Rows.Count, 1).End(xlUp).Row
This does not move my selection box?
How do other macros in the workbook affect creating new macros if they're not being used? I'm now worried about having to account for how many macros i'm making and what's in them o_o
I also tried to copy the code presented and ended up with the 1004 error
ThisWorkbook.Sheets("My Report").cells(rows.Count,1).end(xlup).row. Doesn't like the "row" at the end. I can do "select" and that works. Running Excel 2016.
Chris Demeur Hi Chris,
Please put this value into a variable, cell, messagebox etc. Ex:
myVar = thisworkbook ..... row
Thanks
Dan
lastrow = ThisWorkbook.Sheets("My Report").Cells(Rows.Count, 1).End(xlUp).Row
When putting above code in I get subscript error out of range runtime error 9. Even though my sheet is named My Report....
how can u get the visualbasic window to appear in bottom half of worksheet?
optionsupdate Just collapse it and drag and drop the boundaries. There's 3 buttons in windows applications. leftmost is minimize, middle is expand/collapse, right is red X to close application. Use middle one to collapse and then resize as needed.
thanks very much, I have learnt so much after watching only 3 videos
Awesome buddy. Keep it up!
This is a little unrelated but how do you get the office assistant.. the guy standing at the back?
Roshni Lobo Hi Roshni. I think the guy in the back was from www.incredimail.com - a free email service. But unfortunately he doesn't help with office. Yours, Dan
can somebody please help! I'm writing the code exactly how it is presented but when i'm writing
LastRow = ThisWorkbook.Sheets("MyReport").Cells(Rows.Count, 1).End(xlUp).Row
i keep getting
the Run-time error '1004' Application defined or object defined error
has anybody ran into the same problem?
I think whole numbers like this should maybe be a double? or Long? maybe long is better. Again, I don't use option explicit.
Thanks!
Dan
Your tutorials are awesome and quite intuitive but there are lots of add popping in between for subscription that's very annoying.
is it one (number) or I (letter)? please check it out.
what about this structure:
lastcol = ThisWorkbook.Sheets("My Report").Cells(1, Columns.Count).End(xlToLeft).Column
it works on the same principle; to find the number of the last column without referring to selection
Good luck :)
Thank you!
xlup takes it up to the first cell with border formatted. but i want to keep borders
optionsupdate What do you mean, optionsupdate? Can you show me your code or what is happening?
Dan
ExcelVbaIsFun Sub drugsort1()
'
' drugsort1 Macro
'
'
Range("O52:R71").Select
Selection.Copy
Sheets("Ennogen Goods in").Select
Range("B2660").Select
Range("b99999").End(xlUp).Select
ActiveCell(2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
this is the code, it takes a list from one sheet to another sheet, but I want info posted at first empty row, but xlup seems to make it stop in 5 -10 rows before empty cells, each time I use this the cell gets pasted lower down leaving bigger empty gap.
Yours gratefully.
optionsupdate Would you be willing to show me a sample workbook? You can upload a file here. www.excelvbaisfun.com/contact.html Thanks
ExcelVbaIsFun the file is uploaded, many thanks Sir
editing video for you now. Will upload soon. Thanks! Dan
lastrow = thisworkbook.Sheets("My Report").Cell(row.Count, 1).End(xlUp).row
im getting a object required error 424, how do i fix this, the full code is :
Sub hjj()
lastrow = thisworkbook.Sheets("My Report").Cell(row.Count, 1).End(xlUp).row
End Sub
i have data in column A
never mind i have figured it out, cell should be cells (plural) and row should be rows (plural)
What's wrong with
NumberRows = Application.CountA(WorkSht.Range("A:A"))
Seán O'Nilbud Try application.worksheetfunction.counta rather than application.counta
Dan
Thanks a lot tzbks1234! Will do. Dan
thank u sir =)
Make sure End(xlUp) uses only letters: "l" is a letter; NOT number 1
What other code is in that macro? Or in that workbook? If you don't have room enough to paste it here, post this on our facebook page. You can also put screenshots and even upload the wb on there. Link to that is in the description. We'll help you through it! Dan
Why don't you put last row interested quotation marks; range("a2:c & lastrow")
Because it apart of that cell range. Also when using variable why is it that they do " & lastrow &"
Sorry I meant in* not interested in my first comment
Hi Jayjay F,
lastrow is a variable and thus it changes values. We don't want the word lastrow as part of the range, it might look like this to Excel once it's done calculating: range(a2:clastrow), instead of a2:c20 or whatever value is currently found in the variable lastrow.
Thanks
Dan
ExcelVbaIsFun is it because one is a string and the other is a variable, so range("a1:c20") inside brackets is a string so variable has to be out side quotation marks?
ExcelVbaIsFun is it because one is a string and the other is a variable, so range("a1:c20") inside brackets is a string so variable has to be out side quotation marks?
Lickedy-split.