We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x
Private

Adds rows to the Sheet Automatically

High Priority
71 Views
Last Modified: 2020-04-29
Hi Experts,

I am using the given macro to add 10 rows at the end of the database "Main1DB"
I have also attached the sample file.

Sub AddRows()
    Sheets("BiltyDB").Unprotect "password"
    Application.ScreenUpdating = False
    Application.Goto Reference:="Main4DBLast5"
    Selection.AutoFill Destination:=Range("Main3DBAdd5"), Type:=xlFillDefault
    Range("Main5DBNextRow").Select
    Application.ScreenUpdating = True
    Sheets("BiltyDB").Protect "password"
End Sub

Open in new window


What I wish is that as soon as the Main1DBBlank value is Less than 21, the macro automatically triggers and adds 20 rows at the end.

E.g., What I require is that as soon as some value is filled in cell B10, blank rows in the database will be reduced to 20 and so that will act as a macro triggering event which will
--Unprotect the sheet using Password "Password"
--Clear the Filters, if any
--Add 20 rows at the bottom of the range "BiltyDB"
--enter the serial number in Column A
--Turn the Auto Filters ON
--Protect the sheet using Password "Password" with the following persmissions
   ---Select Unlocked Cells - Allowed
   ---Format Columns - Allowed
   ---Use Auto Filter - Allowed
   ---Edit Objects - Allowed
--Cursor returns to its previous position where it was before the insertion of rows

I also request you to take care on any unforeseen situations which might creep up due to random instances. E.g., what if someone enter some value in B25 instead of B10.
I already faced such situation when the user used the macro "AddRows()" only when two blank rows were remaining in the Main1DB range. In fact that is the main reason that I want the rows to be add automatically instead of user triggered addition of rows.

I used DRAG and FILL as it helped maintain the formatting of entire data range, as the user is not allowed to change the formatting except increasing Column Width.
Regards
Kanwaljit
EEE-Add-Blank-rows-at-the-end.xlsm
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
Hi
Did u noticed that the workbook you poasted have all REF formulas ? Can you post a clean one with no REF to start with !?
Second because what you mentioned of the fact that a user can put data in a lower rows like you mentioned B25 instead of B10 I suggest to have this macro ran when you need it !! ie not automated as it may messup your data.

Like the sub will be programed to do what you want but will only be executed when you press on the AddROw button is that ok with you ??

If yes please start by posting a clean of REF worksheet and will go from there as we need to test that formulas are ok when we create rows like this now it is already messy !!!

Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Thanks Gowflow,
Kindly ignore the REF issues. They will have no impact on the outcome. Believe me on that. But if that is really necessary we can simply delete all the formulas.

What I thought was that If the user is going to put data in rows below the latest blank row e.g., B10 in ours case, macro should give a warning signal that the above row is blank and we need to have at least 20 rows (or lower no of rows, as decided) blank below the row being used.

In face it was not automated and when the user ran it with only 2 rows left, that was the time when the data was messed up and I had to think of an alternatve instead of trusting users intelligence.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
In any case, I have attached a new file. EEE Add Blank rows at the end 2.xlsm
gowflowPartner
CERTIFIED EXPERT

Commented:
ok I have implemented something like this in my office but the user is a bit smart each time she sees close to end of file she activate the button puts in a value say 2000 row and the worksheet increases by this number of course with formulas and validation and all of this its a worksheet that has over 60 columns and more than 50000 rows

Let me see and propose a solution that is workable and at the same time safe and for sure fool proof
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
ok I looked at a bit your file and here are my comments.

To implement what you want to do you need to give the exact same formulas you already have even if you feel its not important, It is very important as if you take the file you just gave me now 02 and you try to copy the last row to a next 20 rows you will see a nice NAME in formulas meaning that there is error in formulas.

Actually I noticed this by looking at your formulas they are quite weired
=_xlfn.XLOOKUP($B18,$K$6:$K$20,$K$6:$K$20,"Detail Pndg",0,1)

Open in new window

Notice this one in Col D always referring to row 6 to 20 absolute so if we copy on of these downward it would make a mess.

Also I am missing _xlfn. what is it ? an add-in ?

Also can I concider that all the shaded rows contain formulas and are protected ?
If the answer is yes then what about Column Q,BH they are both shaded locked and no formulas !!!

PLease asdvise as still this version is not workable 'TRUST ME'

Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Oh, I forgot to tell that I am using Office 365. And it looks like you are opening the file in some other version of excel, as I am not seeing that.

Yes, Shaded rows contain formulas and they are protected. Column Q and BH are supposed to contain static values, which don't seem to change in the near future, so I protected them for speedy entry as protected cells are skipped when protection is on.

And Yes Gowflow, I TRUST YOU.
I still have some of yours solutions and they work great.
Here is the actual file, though a trimmed one. EEE Add Blank rows at the end Actual.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Also It feels that you are right that Users should be careful not to press the button when that should not be pressed.
It seems I over-panicked when I saw the messed up sheet, but now I have second thoughts.

So it would be alternatively great, if you can implement some check to warn and stop the user from pressing button at the wrong moment like when CONTINUOUS blank rows at the end of the range are less than 10 and advise him/her to first add further rows and then keep on working. (Though I am not averse to seeing tricks being done :)
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Okay, It seems something strange has happened.
I have opened, the file I just uploaded. It is opening properly in Excel 2010 with all the macros intact and YES with that _xlfn. thing as Xlookup is not supported in other versions.

But I opened the same in Excel 365, it is saying that the Project is not there. I am not able to see the VBA other than some creepy looking project spaces in Alt+F11
gowflowPartner
CERTIFIED EXPERT

Commented:
You were Kanwal_No2 ?

I have Excel 2010 !!!

Don't hv 365 sorry.
If you want 2010 I can help. Ao check my post and upload a file with 2010 in the sens that I talked and will take it from there.
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
ok Kanwaljit
Here is my solution. Out of experience I don't like to annoy user with too much restrictions although in my mind adding 10 rows is too little but not knowing your business and the frequency of input I will do what you asked but you will see the way it is setup is pretty safe.

1) The file is now password protected already with the password you requested.
2) the macro will monitor input, if the user attempt to write anything below the last line it will give him the Protected warning from Excel as all ROWS below the last line are protected regardless of the column.
3) The user can input data freely up until 'THE LAST ROW' when he input anything in the last raw, the macro will warn him that he needs to add rows and will automatically delete whatever he put in that cell.
4) The user then has no choice but to press on Add 10 Row button, the macro unlocks the sheet copy last row and insert 10 more rows then lock the sheet again.
5) I modified only 1 thing for you which is Col A I turned it into a formula as it will automatically increment the rows when added and the user doesn't have to bother typing this info that is not necessary.

Here is the code in the Sheet Change event that will monitor last row movement
Private Sub Worksheet_Change(ByVal Target As Range)
If IsLastRow(Target) Then
    Application.EnableEvents = False
    Target.Value = ""
    MsgBox "Warning !!! You reached the limit before adding data, please Add Rows.", vbCritical, "Add Rows"
    Application.EnableEvents = True
End If
End Sub

Open in new window


Now this is the Function that checks for Last Row in Module1 and Return True if it is last Row
Function IsLastRow(Target As Range) As Boolean
Dim WS As Worksheet
Dim MaxRow As Long
Dim cCell As Range

Set WS = ActiveSheet
MaxRow = WS.Range("D" & WS.Rows.Count).End(xlUp).Row

For Each cCell In Target.Cells
    If cCell.Row = MaxRow Then
        IsLastRow = True
        Exit For
    End If
Next cCell

End Function

Open in new window



And this is the Routine for Adding 10 Rows. I kept yours same name with Orig at the end.
Sub AddRows()
Dim WS As Worksheet

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.Range("D" & WS.Rows.Count).End(xlUp).Row

'---> Unprotect Sheet
WS.Unprotect "Password"

WS.Range(MaxRow & ":" & MaxRow).EntireRow.Copy
WS.Range(WS.Cells(MaxRow + 1, "A"), WS.Cells(MaxRow + 11, "A")).Insert

'---> Protect Worksheet
WS.Protect Password:="Password", DrawingObjects:=True, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub

Open in new window



Obviously after running the file and testing it the Calculation returned NAME in Col D,E,F,l as they all use this function that is proper to 365 I guess.

Please test it and let me know.
Gowflow
EEE-Add-Blank-rows-at-the-end-Actua.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Thanks a Lot Gowflow !!!
Elaborate, as Always ! It is doing pretty well ...............
Can we Update the code the paste only the special values in Column A instead of the formula. You know one day someone might sort the data and the original order is gone. And if the data has already been sorted by someone the code should not rewrite the entire Column A again. Instead Only the new rows should have the serial number without changing or tempering or re-writing the serial number in column A. But then only the values should be there not the formula.

Can we give the warning a bit earlier ? Living on the edge is giving me jitters. Still not an adventurous being !

One addition, if you please manage. Please add the code to make the entries in Column K in UPPERCase and in Column P to ProperCase

Regards
Kanwaljit
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Gowflow,

I changed the code a bit to increase the IsLastRow to 5 rows. Looks like it is working. You are the better person to judge.

Function IsLastRow(Target As Range) As Boolean
Dim WS As Worksheet
Dim MaxRow As Long
Dim cCell As Range

Set WS = ActiveSheet
MaxRow = WS.Range("D" & WS.Rows.Count).End(xlUp).Row

For Each cCell In Target.Cells
    If cCell.Row >= MaxRow - 4 Then
        IsLastRow = True
        Exit For
    End If
Next cCell

End Function
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Gowflow,
It seems Insert is messing with the Conditional Formatting whereas filldown is keeping it intact.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Gowflow,
Tried some amendment to the code to avoid the CF issue. Please have a look Sir.
Sub AddRows()

Dim WS As Worksheet

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.Range("D" & WS.Rows.Count).End(xlUp).Row

'---> Unprotect Sheet
WS.Unprotect "Password"

'--->Autofill 10 rows at the end
    Application.Goto Reference:="Main4DBLast5"
    Selection.AutoFill Destination:=Range("Main3DBAdd5"), Type:=xlFillDefault
    Range("Main5DBNextRow").Select

'---> Protect Worksheet
WS.Protect Password:="Password", DrawingObjects:=True, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

End Sub
gowflowPartner
CERTIFIED EXPERT

Commented:
Hello just woke up … time difference... I'll take a look at all this but to summarize
Col A no problem
5 before end no problem
Conditional formatting will take a deep look
Special for certain Columns will have to look into that but usually it should be a formulas in validation

Will revert
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
I am thinking while watching this macro … 5 lines before end will give you strictly nothing AAA as user anyway with this code cannot access the last 5 so what is the use of adding 10 lines out of which 5 cannot be used. They are going to tell you what kind of software is this ?? You are mistaking a human reaction where fault is possible to a machine controlled reaction where mistake is not possible (when code is tested for sure). ! row is enough as the procedure of adding rows is just a click of a button its instantaneous. I agree if it would involve a certain task of formatting and copying that would be time consuming but when it is an automated thing no issue.
Let me know ur thought on this issue.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
You are BANG ON as far as the number of lines to be added are concerned, So I have increased them to 100. Infact What if an input box can be given to user to choose the number of rows to be inserted, off course more than 10 and upto a certain maximum !

Instead of using the insert method I used the filldown as it is keeping CF intact. Off course you can manage whatever coding is required, but for my skill level it was the best I could have done. I understand that is not very elegant looking but I had no other option one month back when I created this.
gowflowPartner
CERTIFIED EXPERT

Commented:
ok here we are. These are the changes/fixes this version will do, but before that I want to thank you for highlighting the conditional format issue that I was unaware of. It is truly a big discovery for me to be able to copy/paste rows without actually copying them WOW ! Am impressed.

Here are the details:

1) First I fixed all the conditional formatting that was in this file (maybe not important to you as maybe is a test but important for the file itself to make sure all references are good). The file you had last posted had already this mess in conditional formatting that also my previous routine did not help adding more.

2) I modified the procedure to use filldown but not the way you suggested as the named formula you had pointed to the last 5 rows that if we had to use would have created an other catastrophe as these rows are used except the last one.What I did actually is simply removed the Insert and replaced it by Filldown, that's not all I started from MaxRow and not MaxRow + 1 and most importantly I REMOVED THE COPY !! and this was the big trick. Thanks again for pointing in this direction.

3) I introduced a prompt to choose number of rows to insert limiting it to max 100 with the possibility for the user to change his mind and cancel the procedure which would exit without any change.

4) Also introduced the Upercase for Col K and Propercase for Col P that you will find in Worksheet_Change event.

5) Last but not Least, I removed formulas from Col A and unlocked Cells and converted formulas to values and in the AddROW procedure the rows will take the indexes as values. It is worth noting here that even if your users Sort the whole worksheet and you endup in last row with Sr No 1 or any the routine will look for the highest number in this column and will add the new rows + 1 from that number so that you don't get any duplicates.

Here are the pertinent codes

Worksheet_Change event
Private Sub Worksheet_Change(ByVal Target As Range)

'---> Protect Last row from Input
If IsLastRow(Target) Then
    Application.EnableEvents = False
    Target.Value = ""
    MsgBox "Warning !!! You reached the limit before adding data, please Add Rows.", vbCritical, "Add Rows"
    Application.EnableEvents = True
    Exit Sub
End If

'---> Validate Col K and P
'Column K in UPPERCase and in Column P to ProperCase
Application.EnableEvents = False
If Not Intersect(Target, Columns("K")) Is Nothing Then
    Target.Value = UCase(Target)
ElseIf Not Intersect(Target, Columns("P")) Is Nothing Then
    Target.Value = Application.WorksheetFunction.Proper(Target)
End If
Application.EnableEvents = True

End Sub

Open in new window



Add Rows Procedure
Sub AddRows()
Dim WS As Worksheet
Dim lSerial As Long, I As Long
Dim sRows As String

'---> Prompt for Number of Rows
Do
    sRows = InputBox("Please enter number of rows to add max 100", "Add Rows", 10)
Loop Until (IsNumeric(sRows) And Val(sRows) <= 100) Or sRows = ""
If sRows = "" Then
    MsgBox "Procedure Add Rows cancelled by user.", vbInformation, "Add Rows"
    Exit Sub
End If

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

'---> Set Variables
Set WS = ActiveSheet
MaxRow = WS.Range("D" & WS.Rows.Count).End(xlUp).Row

'---> Unprotect Sheet
WS.Unprotect "Password"

'---> Number Col A Find Highest Serial used and increment
lSerial = Application.WorksheetFunction.Max(WS.Range("A6:A" & MaxRow))
lSerial = lSerial + 1

'---> Copy last row and fill down
WS.Range(WS.Cells(MaxRow, "A"), WS.Cells(MaxRow + Val(sRows), "A")).EntireRow.FillDown

'---> Number Col A Find Highest Serial used and increment
For I = MaxRow + 1 To MaxRow + Val(sRows)
    WS.Range("A" & I) = lSerial
    lSerial = lSerial + 1
Next I

'---> Protect Worksheet
WS.Protect Password:="Password", DrawingObjects:=True, AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True

'---> Disable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Open in new window



Workbook have been updated with all the last changes. Please try it inside out.
Gowflow
EEE-Add-Blank-rows-at-the-end-Actua.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Thanks Gowflow !
Thanks a Lot for the Nice words of encouragement. But to be Honest, I know nothing about VBA, as you must have known from my questions. So if I did anything good, that has to be a fluke.

A Quick response for
Point No 4
4) Also introduced the Upercase for Col K and Propercase for Col P that you will find in Worksheet_Change event. 
Worksheet Change event is unloading the Undo Stack So I need to edit them to Workbook Open event.


Point No 5
5) Last but not Least, I removed formulas from Col A and unlocked Cells and converted formulas to values and in the AddROW procedure the rows will take the indexes as values. It is worth noting here that even if your users Sort the whole worksheet and you endup in last row with Sr No 1 or any the routine will look for the highest number in this column and will add the new rows + 1 from that number so that you don't get any duplicates. 
The sheet is protected and Only above 4 permissions are allowed to the user. So the possibility to sort or delete is not there. So I used the filldown to extend the serial number.
QUERY : Which one is faster ? Using AddRow procedure or filldown to enter the serial numbers (assuming user is not going to sort the sheet) ?


The following code is in BiltyDB Code Menu. Is it relevant ?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Thanks for the enhancements !
I really appreciate going beyond the call of duty !

Regards
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
Sorry but point 4 and 5 I did not understand what do you need me to do ??

QUERY : Which one is faster ? Using AddRow procedure or filldown to enter the serial numbers (assuming user is not going to sort the sheet) ?
What do you mean ?? What I wrong in what I did in AddRow ??
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
I don't need you to do anything. Just my feedback on that.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
In Point No 4.
Uppercase and Propercase needs to be done when the file is opened
gowflowPartner
CERTIFIED EXPERT

Commented:
but you are asking this
QUERY : Which one is faster ? Using AddRow procedure or filldown to enter the serial numbers (assuming user is not going to sort the sheet) ?

What do you mean by this I don't understand the question
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Uppercase needs to be done when the file is opened
What's the problem doing it where is now being done ?

You said ur not pro in VBA just tell me what you want I tell you were is the best place to do it. For sure provided I understand what you do and what you want !!
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
QUERY : Which one is faster ? Using AddRow procedure or filldown to enter the serial numbers (assuming user is not going to sort the sheet) ?
Just to know is there any significant speed difference in execution of macros, if there are about 5000 rows ?
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
What's the problem doing it where is now being done ?


Undo Stack of Unloaded
gowflowPartner
CERTIFIED EXPERT

Commented:
PLease let's tackle 1 issue at  a time !!!

Just to know is there any significant speed difference in execution of macros, if there are about 5000 rows ?
Just do this

go in the routine AddRow and replace 100 by 5000
SAve the file and exit
Run the file press on Add ROws and put 4900 and see how fast it is

That is for that.
Gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
I just did instantenous less than 1second for 6000 !!! so GREAT !!!! thanks to u
:)

Now tell me about this
Undo Stack of Unloaded
What on earth is this ??

Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
No of entries which can undone or redone
Undo or Redo
Ctrl Y and Ctrl Z
gowflowPartner
CERTIFIED EXPERT

Commented:
do you have a routine for that ? I am lost please be specific what in he code that I wrote is giving you trouble please provide an example o I understand !!! I do not read minds.

Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Suppose I enter 1,2,3,4,5 in Cell J14, J15, J16, J17 and J18
I can undo these entries by pressing Ctrl Z
But if after entering those entries I go to any cell in Column K and enter anything, worksheet change event is triggered and my ability to undo those entries is gone.
gowflowPartner
CERTIFIED EXPERT

Commented:
OK This is functionality of excel not a special macro and every time CTRL Z it will undo the last entry then the one before then the one before etc...

Right or I am mistaken ?
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Yes, this is functionality of Excel Which is taken away when the Macro is Run. This is what I know or I really believe 
gowflowPartner
CERTIFIED EXPERT

Commented:
Let me think and do some trials I never give up on challenges. I'll get back to you … working on it
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Thanks Gowflow.
But I don't want to waste yours time and energy. If you are going after this, then you must search for
https://excel.tips.net/T002060_Preserving_the_Undo_List.html 
https://www.youtube.com/watch?v=QYpPgfBs1Zk

These are the old links. But I am not sure whether Microsoft has provided any cure for this. AFAIK there is none.
gowflowPartner
CERTIFIED EXPERT

Commented:
Well I just saw your 2 link up here but did not check them but what I found very quickly is by running a macro (Any macro !!!) and you have quite a few in your workbook, then clear the undo list YES.

So its your choice, What we can do (but I find it a bit annoying) is to ask the user before running the change events do you want to undo any previous actions ??? if he says yes then we jump out of the procedure

Check out this file type anything in any column except Col O and try the CTRL Z then type in Col O and follow instructions … worth trying.

By the way I didn't know about CTRL Z either !!! I am the worst user :) :)
GOwflow
Sample-Preserve-Undo.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
By the way I didn't know about CTRL Z either !!! I am the worst user :) :)
That shows you do only the Master Stuff :)

I happened on the Examination eve of my Chartered Accountancy Examinations final year. While Studying for Financial Accountancy exams I suddenly noticed that for the Last Five Years I have been spelling Depreciation as Depriciation :)

So its your choice, What we can do (but I find it a bit annoying) is to ask the user before running the change events do you want to undo any previous actions ??? if he says yes then we jump out of the procedure
Another way to do this I feel is to trigger the event via Workbook Open like the following, but I am not sure How to pass the sheet name in the macro.
Private Sub Workbook_Open()

Application.EnableEvents = False
If Not Intersect(Target, Columns("K")) Is Nothing Then
    Target.Value = UCase(Target)
ElseIf Not Intersect(Target, Columns("P")) Is Nothing Then
    Target.Value = Application.WorksheetFunction.Proper(Target)
End If
Application.EnableEvents = True

End Sub


Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
User is interested in typing fast. But denying him the Undo capability is not doing justice to the cause.
The user does not mind entering data in lower case or upper case or mixed case. It is on my own inhibition that I wish to maintain the uncharted English rules for entering the data. So It would be great if when the workbook is opened the first job the macro would do is to convert the text in the specified columns to UPPER Case or Proper Case as per our wishes.

In that way both the users purpose and our purpose would be solved. Both parties are happy
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
I am recording a small video to show one bug which I don't understand.
gowflowPartner
CERTIFIED EXPERT

Commented:
ok so we do it on workbook before close let me write the routine and will post a new version.
Ok just noticed about the video
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Don't do it workbook before close, as the user might be interested in Not saving the changes made by him. But it we do it via Workbook Before Close, there might be some forced save changes.
So I feel it should be done only one Workbook Open only.
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
ok but not the way I wrote it. I will write the routine and post a new workbook. Waiting for your video to fix all at once.
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Sure. Give me Five minutes. It is regarding the Upper Case and Proper case routine.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Here is the Video. If we enter multiple Value or Delete multiple values in Target Column, debug appears. Then if we close the file even without saving, the macro does not work. I have to close Excel and restart. Then the macro will again start working. This is also a big reason I wish it to be on Workbook Open. Let the user do their stuff. We will do our stuff in our own way. In fact there are a lot of things and Check I wish to perform when the file is opened.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
I will not be on my screen for next 20 minutes. Thanks.
gowflowPartner
CERTIFIED EXPERT

Commented:
Yes no problem. Here is the fix
GOwflow
EEE-Add-Blank-rows-at-the-end-Actua.xlsm
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Everytime I download the file uploaded by you and open it for the FIRST time. I get at error message.

Run Time Error5
Invalid Call Procedure or Argument

Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
As far as Debug issue on addition or deletion of multiple values in Target Column is concerned, that seems resolved.

But the Memory Stack, off course, is still cleared. So it would help if that portion of the Code is moved to Workbook Open event.
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Just for Academic Purpose. Might be relevant for you.
While Adding 9000 rows, ADDRow routine took 5 Seconds, and filldown took 2.38 seconds.

I use FastExcel Add-in of Charles williams of www.decisionmodels.com and it contained a Time Macro Utility.
gowflowPartner
CERTIFIED EXPERT

Commented:
I use FastExcel Add-in of Charles williams of www.decisionmodels.com and it contained a Time Macro Utility.

Well here we are getting too specific. !! I don't develop based on special Add-ins. For sure htose speed up things anyway. Will post shortly the version with open workbook.

I guess you get the error coz the file I post is saved under 2010 and you use 365 and after you save first time then the formulas get recognised and all works fine then.

Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Well here we are getting too specific. !! I don't develop based on special Add-ins. For sure htose speed up things anyway. Will post shortly the version with open workbook.
I understand. That was pure Academic.
I guess you get the error coz the file I post is saved under 2010 and you use 365 and after you save first time then the formulas get recognised and all works fine then.
Got your point. Thanks. Now I understand why it works fine after the first opening.

Thanks a Lot !
I will check in tomorrow morning.
Good Night Sir !

Partner
CERTIFIED EXPERT
Commented:
ok here it is. The Sub CaseFix here is the code I put the reference in Workbook Open the sub is in Module1 and I removed the part in Worksheet_Change event that concerns this routine.


Here is the code
Sub CaseFix()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long

'---> Disable Events
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

'---> Set Variables
Set WS = Sheets("BiltyDB")

'---> Change UpperCase in Col K
MaxRow = WS.Range("K" & WS.Rows.Count).End(xlUp).Row
For I = 6 To MaxRow
    WS.Cells(I, "K") = UCase(WS.Cells(I, "K"))
Next I

'---> Change ProperCase in Col P
MaxRow = WS.Range("P" & WS.Rows.Count).End(xlUp).Row
For I = 6 To MaxRow
    WS.Cells(I, "P") = Application.WorksheetFunction.Proper(WS.Cells(I, "P"))
Next I

'---> Enable Events
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Open in new window


Please check and let me know.
Gowflow
EEE-Add-Blank-rows-end-actual-V04.xlsm

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Above Code will change the case only if the active sheet is BiltyDB at the time of closing of file.
If any other worksheet is active at the time of closing of file, then that worksheet will be active while opening the sheet and the code is not converting case in such a scenario.
Other than that everything feels quite smooth.
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
Fixed I updated the code. Sorry this is what happens when it becomes late !!! Refresh my comment
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
There we Are ! The journey was Long, but the results are soul satisfying ! No words to Say Thank You !
Please be Safe ! Live Long ! We need You Sir !
Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
Tks Kanwaljit for your nice words and thoughts. Also stay safe and feel free to let me know anytime you need help.
Regards
Gowflow
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Hi Gowflow,
I am facing one major issue while entering data.
The user is not supposed to enter data in the LAST ROW in the data range, saying row number 53 (or Sr No 48) in the attached file. But If I apply filter on the data then the last row in the filtered data becomes the LAST ROW and I am not able to enter data in Row 49 (or Serial No 44) in the attached file.

Somehow the code is treating the LAST VISIBLE ROW (row 49) as the LAST ROW in the RANGE (row 53) and triggering accordingly.
It seems that .End(xlUp).Row is causing the issue in ours case.
MaxRow = WS.Range("D" & WS.Rows.Count).End(xlUp).Row
If you find it useful, I have a defined named range "Main4DBLast" which refers to the last row in the RANGE (row 53)
EEE-Add-Blank-rows-end-actual-V04.xlsm

Kanwaljit
gowflowPartner
CERTIFIED EXPERT

Commented:
ok willing to look at this but as the thread in this question is very big and the question is closed please open a new question put the link here and I will be glad to assis. Keep the explanation on the new question very brief.
Gowflow
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.