Adding And Deleting Columns, Ruins Code
Jul 21, 2008
I have a spreadsheet with 20 or so columns but that number can change at any time. I need my code to by dynamic so if a column is deleted (or added) my code will still work.
Each column has a name in row 2 and I'm trying to use that in order to make everything dynamic. For example, my code counts the number of blue cells in colums D4:G68, but now if someone adds a column before column D, everything gets messed up and it will still count the cells in D4:G68 but I want to use E4:H64 now. Does that make sense? I have made code that searches the column names and returns the column number to correspond to the name. How would I change this?
Range ("C8") = "= CountBlue(E4:G68)"
the code doesn't look exactly like this, I believe it is actually in R1C1 format
View 9 Replies
ADVERTISEMENT
Aug 31, 2007
I need to stop users from adding and deleting sheets in a workbook
My idea was obviously to disable the command bars to add or delete sheets in open event and then put back in before close.
But then i thought...whats stopping them from right clicking the sheet tab and inserting a sheet, can i remove that menu to...?
I also wasnt sure if there were short cut keys to add or delete sheets.?
Some of my users use excel alot so i want to account for an tips they know that i might not.
View 9 Replies
View Related
Jun 16, 2008
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies
View Related
Sep 26, 2006
I'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies
View Related
Oct 24, 2008
I am trying to write a VBA code in excel to delete any columns when a cell in that particular column = 0 (zero). That cell would start at B19 and subsequently follow to C19, D19 and so on.
View 9 Replies
View Related
Dec 19, 2013
I have attached a copy spread sheet. This has been working great but i have been asked to add some items and i dont want to screw up the working functions.
I now want to add incert two columns so the actual costs of a first and second service can be added to the contracts and used contracts sheets, this information allong with data from a,b,e,g h needs to be copied over into a new sheet (report sheet) which will have the budgeeds costs in column i,j starting from row 3 and finding the last row so as not to overtype so that a report can be sent showing profit/loss.
The costs will be put in at diferent times so it only need to up date a changed cell
If i just add columns will this effect the auto archive coding? Could the data be copied over to the new sheet using the original code on start up? (so customer etc copied then as cost are put in these would be added to the respective rows on each start up.
I have had to remove some of the sheets to up load this so my not work correctly, but you can see the funtion in the code
View 3 Replies
View Related
Feb 11, 2013
I am working on a worksheet where users are asked by a userform combo box to select a product. When they select the product a picture of that product appears in the top left corner of the worksheet.
I have managed to get the picture to appear however delete any pictures that were in that cell before pasting a new one, cause when changes the product a picture is pasted over on top of the previous one however if the pictures are not the same size you may see the previous under the current.
delete any previous pictures. Here is the code I have so far.
Sub GetPicture()
Set Rng = Sheets("Products").Range("A2", Sheets("Products").Range("A2").End(xlDown)) '.Resize(, 10))
[Code].....
View 3 Replies
View Related
Jul 9, 2013
I have a spreadsheet with multiple similar entries I would like to add up. What would work well for me is to be able to input a column like a and a target column like e.
So in this case it would search throughout column a and wherever it finds a duplicate entry add column e and then delete one of the rows. I could then run it again under different column criteria if needed.
So if I had the below sheet:
I could search by column a and add up column h to read like this:
View 1 Replies
View Related
Oct 24, 2006
I am trying to create two macros. The first macro will add a button, and the second macro will delete the button. I am noticing that when I add the button in the first macro, Excel is automatically naming the button (i.e. Button 26), and then when I try to delete the button with the second macro, this button name is not recognized. How can I name or set the button as a variable so that I can successfully run these two macros?
View 6 Replies
View Related
Nov 9, 2008
I am using several formulas to conditionally format and fill in rows from column A to column BS. These are the formulas and application areas:
=$BS4<>"" Applied to =$A$3:BS$350
=$G3<>"" Applied to =$A$3:BS$350
=OR($J3="Regional Manager",$J3="Assistant Manager",$J3="Manager") Applied to =$A$3:BS$350
=OR($J3="Recruiting Manager",$J3="Owner",$J3="District Manager",$J3="Office Manager") Applied to =$A$3:BS$350
If I do not add or delete any rows, everything works just fine. The problem I am running into is that this list is constantly updated and rows are being added and deleted. When I do this, it duplicates my rules and changes my applied to ranges. Here are some examples of the changes:
=$N$3:$BS$3
=$A$3:$M$63
=$N$4:$BS$64
This creates much excess work, when I have to go back and change everything back to the way it should be. how I can either modify my formulas or application areas to correct this situation?
View 4 Replies
View Related
Jul 21, 2014
I currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...
View 7 Replies
View Related
Mar 26, 2014
I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.
I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:
1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table
The code I'm using for my "Add" button is:
[Code].....
The code I'm using for my "Delete" button is:
[Code] .....
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above).
Attached File : Productivity Report (HH).xlsm
View 8 Replies
View Related
Jul 21, 2014
I currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...
View 9 Replies
View Related
May 21, 2006
ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.
View 9 Replies
View Related
Mar 18, 2014
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
View 1 Replies
View Related
Sep 2, 2006
Is is possible to use VBA to remove/delete a macro and also remove code like this on worksheets:
Private Sub Worksheet_Change
End Sub
Private Sub Worksheet_Activate()
End Sub
View 4 Replies
View Related
Sep 9, 2009
I am now trying to combine 2 sections of code but being a complete VBA novice I'm stuck, where should the second Sub go so that it works,
View 4 Replies
View Related
Aug 15, 2012
I have a worksheet and the products starts from the first row has product name and 2 following rows are other information which i do not need. i was trying to figure out a VBA code that can leave the first rwo and delete every 2 rows below the first one and then to complete this until the end of the data.
For example: in the following rows, i want to keep rows 1, 4, 7 and all others to be deleted and this process to continue until the last rows having data.
Column A
1 Product name
2 serial number
3 manufucture date
4 Product name
5 Serial number
6 manufucture date
7 Product name
8 serial number
9 manufucture date
Sub Deletesecondtworows()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
View 4 Replies
View Related
Dec 26, 2008
Deleting Columns. I've got 2 codes here,
This:
View 3 Replies
View Related
Jan 23, 2013
I've a file with lots of data. In that file I've some blank rows, I want a code which search complete blank row and delete.
View 2 Replies
View Related
Jul 23, 2013
My following code keeps deleting leading zeros. The purpose of this code is to delete leading spaces but leave the zeros.
Code:
lr = Cells(Rows.Count, 1).End(xlUp).row For Each c In ActiveSheet.Range("A2:A" & lr)
If Left(c.Value, 1) = " " Then c.Value = Right(c.Value, Len(c.Value) - 1)
c.Value = c.Value
Next c
View 2 Replies
View Related
Jan 11, 2008
I have a template that when it is opened it will automatically save the file to a specific location with the date in the file name.
Currently my code is
Private Sub Workbook_Open()
ActiveWorkbook.SaveAs "G:Shared"IT_New " & Format(Date, "mm-dd-yy") & ".xls", , , , , , xlShared
End Sub
Does anyone know a way to have this code disabled (either by deleting or making it all a comment) on the saved file (xls) so the speadsheet does not try and overwrite itself when I go back and open a previous days spreadsheet?
View 9 Replies
View Related
Aug 19, 2008
I've got a row of data with a column showing some sales including amounts with 0. Any row with 0 I want gone, so I want the macro to filter the list, delete all the rows with 0 in that column, and then display what's left, all the rows with some number other than 0 in that column.
The code below should filter the 0 rows, delete those rows, and then display what's left. I don't want the heading row deleted, but everything below it that is filtered. What my code below is doing now is not deleting everything it should. How can I fix it.
Sub prepmetrics()
'
' prepmetrics Macro
' for use with monthly report - don't forget the new coversheet
'
Application.ScreenUpdating = False
Range("A7").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:="0"
' this is the challenge here. How do I tell Excel to select, relatively, whatever
' rows are now showing as a result of the filter?
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
' there may be a number of different criteria in different columns, so
' we rinse and repeat as many times as necessary but, again, each filter/delete
' task should delete all rows filtered
Selection.AutoFilter Field:=16, Criteria1:=">7500"
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Aug 14, 2008
I have an excel spreadsheet with Extra columns that have no data in them. I want to delete this columns so that there is no horizontal scroll, but highlighting and right-clicking and choosing delete isn't working. Not sure why. How can I delete?
View 2 Replies
View Related
Mar 24, 2014
I tried to write a macro for deleting some columns automatically in my workbook
- it will be applied to all worksheets, except two worksheets called "samsung" and "toshiba"
- columns will be deleted from V to AA and AU to AZ in the related worksheets
Please check my attached file and my macro inside it.
deleting columns makro.xlsm
View 5 Replies
View Related
Jan 28, 2010
I want to delete columns that contain a 0 value in row 2 of the column.
In my case, I have headers in row 1 and values in rows 2 and below. I eventually want to filter out the columns that contain no values below the headers.
Let me know if you need any more information for clarification.
View 10 Replies
View Related
Nov 23, 2007
I am trying to build a macro to delete the cell content for the entire A column, B column, C column. Then i would like the macro to delete the cell content from row 3 to the bottom of the sheet.
View 9 Replies
View Related
Jul 13, 2009
I have this code that I have had for a while.. it works okay on my computer and does as intended... it deleted all empty rows in the selected range after checking to see if any cells contain anything that makes it look blank but isn't (it cleans those cells).
So on my computer it works... on a coworkers computer it converts every used cell in the usedrange to #VALUE...
View 14 Replies
View Related
Jun 27, 2007
I have used the following Macro that deletes the Macro code. It then saves the file and quits excel.
*************************************
Sub removeAllCode()
Dim awi
Dim awcl As Integer
Dim count As Integer
Dim i As Integer 'loop counter
On Error Resume Next
count = ActiveWorkbook.VBProject.VBComponents.count
For i = 1 To count
Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i)
awcl = awi.CodeModule.CountOfLines
awi.CodeModule.DeleteLines 1, awcl
Next i
Set awi = Nothing
ThisWorkbook.Save
Application.Quit
End Sub
*************************************
However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists
If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.
I have tried various examples listed in different forums/sites but nothing works.
View 9 Replies
View Related
Apr 11, 2007
I have used the code that was previously suggested in this forum for deleting a module. I have module 2 looping into module 1.At the end of module 1
I tell it to delete itself,likewise for module 2.
I placed in the "this workbook" on open event, the code
Application.Run" CALENDAR.xls!TEST
In the "this workbook" I have also put code in the "before close" event:
If Me. Saved=False Then Me.Save
All is great until the end user reopens the workbook and it locks up on the workbook open event because there is no modules to run now.
View 6 Replies
View Related
