Posts by Firann

    Re: Working Area expanding each time Rows are added and then deleted

    Thanks for the reply!

    I have attached a sample of my schedule. If you delete rows using my Delete button on table 1 (I have a security check so not everything can be deleted) you will notice that the amount of rows deleted will appear at the bottom after row 104. Similarly if you add rows and then delete them the same thing happens.


    I have a weird issue on 1 specific excel file. I created a new working based on a pre-created template I use for all my workings, and while all others work fine this particular one has the following problem:

    I have my Working Area set to Row 104 and everything after that Hidden. If for example I add 2 lines after line 80 my active sheet correctly renumbers my last row to 106. If I now delete these 2 added lines, my previous last row is renumbered to 104 correclty, however 2 rows are also unhidden and the Working Area remains as up to row 106.

    Again as I have stated this issue is not present in my other Workbooks/Worksheets which are based on the same template.

    Does anyone have a solution to this problem? I wish for my Working Area to dynamically change and keep my original 104 row as last.

    Re: Deleting Rows in a Selection of Cells where Column A has a specific value

    Quote from KjBox;777380

    Here is a sample workbook with my code working.

    I did say this in my first post:

    Thanks for your time in providing sample workbook. I understood what you said about it checking the entire workbook. What confused me was that it was Deleting rows WITHOUT 1 instead of WITH 1 despite my edit clearly stating that if =1 then delete.

    I tested around a bit more on my own and finally got it to work.

    Delete code

    For some reason in the Loop process if I use With Cells (Lrow, "A"), instead of Range(), and I have N64 to N70 selected it was checking cells N127 to N139 instead of A64 to A70. It was calculating Lrow for Cell address as Row + Previous Row. It works perfectly with Range() however can anyone provide a reasoning for the .Cells() method not working?

    I'm pretty happy with the code however if anyone has any suggestions for better optimization your input is appreciated!

    For reference, if anyone wants to use it, I am also attaching my InsertRow Code which was made to work with the provided DeleteRow Code

    Re: Deleting Rows in a Selection of Cells where Column A has a specific value

    Quote from KjBox;777378

    Hey KJBox!

    Thanks for the quick reply.

    I have tested the coded and have the following notes:

    1. If Column A has no values it deletes everything irrespective of what cells are selected.
    2. If all cells in Column A have values, when I select specific cells it will delete the selected cells and remove the value of all other cells.


    I modified the code provided as follows:

    The weird thing that happens is that it deletes all rows WITHOUT 1 instead of rows with 1. I assumed that the above code tells it to check whether cell Ax has a value of 1 and if so delete it but I'm confused as to why the opposite is occurring.

    Greetings all!

    First post here even though I've used the forum for information tons of times. Thanks to everyone that has provided useful information!

    Now on to the problem.

    [Disclaimer] All Subs have been found through various posts / websites and slightly modified to suite my needs. I do not claim to be the creator of them.

    I want the users to be able to select multiple rows they wish to delete and then check for each row whether the value in the cell is 1 for it to be deleted. (value of cell does not matter to me as it is only to be used as a security measure so they can delete only added lines and not the pre-existing rows I have insert).

    I have tried two VBA code

    The one from OZgrid ( which I am currently using to delete everything has been modified slightly, however I am unable to modify the If WorksheetFunction.CountA(Selection.Rows(i)) = 0 check specifically for the value of Column A cell:

    The second code which works for checking the cell in Column A, I am having trouble with setting the range to be that of my selection. By default is uses the .UsedRange and I can get it to work perfectly if I set the FirstRow and LastRow to specific lines for testing, however it does not work if Selection.Rows to set First and Last row ( 'Set the first and last row to loop through). The code is:

    Finally while not in the Thread, since i do not want to flood the forum with threads, I am posting you the code I have to insert any amount of lines based on the row selection. I require your help in setting it up so it changes the value of cell in Column A to the value which will be looked for by the delete sub (e.g. 1). I can only get it to modify either the original row (which should stay blank) or only the last one. If you have any suggestion for different code I am open to suggestions, provided it lets users defines how many rows to insert and that all new rows are exactly the same as the selected row, except Cell in Column A which should have the defined value.

    Thank you for your help :)