Prevent Formula #REF! When VBA Macro Deletes Columns
Aug 5, 2009
I'm using the following code to delete columns:
Dim rng As Range
Dim i As Integer, counter As Integer
Set rng = Range("1:1")
i = 1
For counter = 1 To rng.Columns.Count
'If cell i in the range contains an "x",
'delete the column
If rng.Cells(i) = "x" Then
rng.Cells(i).EntireColumn.Delete
Else
i = i + 1
End If
Next
My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?
I need a macro that searches my spreadsheet for a keyword in Column B. If it finds the keywords (or an array of keywords would be even better), it then deletes the entire row above the keyword, the row with the keyword, and the row below the keyword.
If the macro finds the text in column 2, it would delete 1, 2 and 3.
I need to have a second macro that performs a very similar function. it copies the 3 rows (similar to the example below) to a different sheet based on the keyword.
I’ve got a macro that automatically starts when the workbook is open. The macro finishes with
Application.Dialogs(xlDialogSaveAs).Show
The document will be always saved with a new name. As I just need the macro once when the workbook is created I’m looking for a code that deletes the macro before the workbook is saved. I tried to record a macro while I was deleting another macro, but apparently it is not working (the recorded macro is empty). Does anyone know if there exists a code to delete a macro?
i have this macro made by another member here that looks for "T" in column 10 and deletes entire row if in that row has a "T". I need it modified to also delete if it contains "FT" and also erase if cells is blank.
Sub Macro8() Sheets("FORMERS").Select 'deletes entire rows based on a text on one row (this case errases all that have a "T" in column 10 (column J)
Dim DeleteValue1 As String Dim rng1 As Range Dim calcmode1 As Long With Application calcmode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With...........................................
It takes all numbers in column 4 starting with Row 15 and deletes all duplicates. It then shows the number of times the number was duplicated and puts this number in column 3. MY PROBLEM: The macro searches each line and takes FOREVER! I have data with thousands of lines. I already tried the screenupdating method which really doesn't help that much. Is there possibly a better code for doing this?
Sub Factor() Dim sID As String Dim sOldID As String Dim lLastRow As Long Dim lrow As Long Dim lcount As Long Dim lLoop As Long lLastRow = ActiveSheet. Cells(Rows.Count, 1).End(xlUp).Row lrow = 15 sID = ActiveSheet.Cells(lrow, 4).Value sOldID = "ActiveSheet.Cells(4, 15).Value" lcount = 1 lLoop = 1 Do While Len(sID) <> 0 If sID <> sOldID Then If lLoop = 1 Then.................................
I have a macro that that stops every time I’m trying to delete the sheet. I get this message. “Data may exist in the sheet(s) selected for deletion. To permanently delete the data, prese Delete.” and I have to manually click “delete” to continue running the macro. How can I avoid this stop so that macro runs thru it without stops?
I need help writing a macro. I only have experience recording them, not writing them in VBA.
I need to check columns A and B for blanks. If A and B are both blank, I want the macro to delete the row and move on to check the next row. I need to perform this macro for the entire worksheet.
I need a Macro that deletes all cells in column D of all worksheets in a book that are equal to the word "Timeout" and to shift the cells to the right of the "Timeout" cells into the deleted cells but leaving all other rows where the word "Timeout" is not present.
So in the case of Rows 2, 4, 6 and 8 containg the word "Timeout" in column D, I would like cells D2, D4, D6 and D8 deleted and the remaining cells shifted to the left but cells D1, D3, D5 and D7 left as they are.
I am trying to parse and remove unwanted rows from a very large text file using At the moment, the application runs rather slow and was wondering if the experts could give some pointers on how to make the code more efficient while still keeping it simple so others may be able to modify later. I am keeping the ScreenUpdating True as the alternative false will just show Excel as Not Responding to the user until the VB is finished.
Sub deleteReplaceRows() Application.ScreenUpdating = True Dim DeletedRows As Integer Dim lastRow As Long Dim Arr(7) Arr(1) = "<PUZZLE>" Arr(2) = "<%" Arr(3) = "%>" Arr(4) = "Response." Arr(5) = "</PUZZLE>" Arr(6) = "<HINT>" Arr(7) = "<MESSAGE>"
For i = 1 To 7 Do Set rng = Columns(1).Find(Arr(i)) If rng Is Nothing Then Exit Do rng.EntireRow.Delete DeletedRows = DeletedRows + 1...................
how to put together a macro for a command button that deletes rows with certain text in a docyment - which worked fine a week or so ago - but now the macro will not work.
I made changes to the document - but made sure i also made changes to the code.
This is weird - if you delete a sheet that contained a control then
a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End b. public variables lose their value
These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
I am trying to create an invoice but the invoice show #N/A in certian columns when there is no number which already have formulas, how do I modify them using the if function?
When you put a label in a column, that is wider than the column, and you don't wrap it or shrink to fit, etc. it "spills" into adjacent columns so the whole label is displayed. In some cases this might be desirable, but in others not.
You can prevent it from spilling by putting a blank space into the next adjacent column -- that truncates the display of the label -- but clearly this is not a good solution.
Is there a way to prevent labels from doing this so that only the portion of the label that is in the cell where the label is entered is visible on the spreadsheet? Of course you can see the entire label by selecting the cell itself, and that's what I want to do without it spilling into other columns.
I have a sheet that uses Blocks of cells eg: A1:X10,A12:X22 etc to hold data/text etc.
I am filling colums A to V with data and Ranking and summing them in W & X, I don't need to show K, L M ...for example, until they are totally filled, so I hide them manually until needed.
Have a Macro to print "results" A1:X10,A12:X22 etc, and I set a "print area" or "print selected"...works fine to fit to 1x1 Page.
Problem: If I hide L, M, N etc, the macro runs fine but still uses the unhidden size of the selection to fit the page ie: I get A-B-C-D...-W-X as required, but the other half of the page is blank, because the size selected is for all including the hidden columns.
Q: How can I (Simply) print what's visable to 1x1 Page, fitting more in each time I unhide a column or two.?
I am instantiating Excel 2003 from vb.net and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?
One of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.
To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.
This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected! WHY!
I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.
I have a userform with several textboxes and 2 comboboxes. The data that I need protected from duplication is based in the comboboxes. If I ignore the other fields and presume the following it may be easier to understand:
The comboboxes are populated by the data stored in sheet1 and when the userform is complete, the OK button populates all the data into sheet2
Combobox1 contains names eg, John, Julie, Bob etc Combobox2 contains colours eg, Red, Blue, Green etc
I want the userform to allow the data to be entered into sheet2 only if it is not an exact duplicate of the choices in BOTH of the comboboxes
For Example John Red Julie Red Bob Blue John Blue
These are all ok, however, to try and enter John Red again would bring up a message box indicating a duplicate entry and prevent the data from being entered.
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
What formula can I use to accomplish the following, if it can be done.
If B9 is 100% all the rest of the % cell inputs would be 0%
If B9 is less 100% ,lets say 50% all the rest of the % cell inputs would be less than or equal to 50% until any sum of those cells equal to 100% in which case if I enter 50% on B9 and 25% on D9 and 25% on F9 their total sum would be 100%.
If I try to enter a % on H9 it should not allow me to do it because then the total sum on O9 would be more the the original amount on A9
The attached file contains hypothetical data on a basketball player's success from the free throw line. It lists the number of free throws attempted and the number made and then divides the number made by the number attempted to calculate the free throw percentage. However, I keep getting a # DIV/0! error in the games free throws weren't taken. How do I write the code so that when the error occurs, a message is displayed to the effect that no percentage can be reported because no free throws were attempted by checking the cell in column D? How do I use the IsError function to figure this out?
I'm creating a maths workbook in Excel. I want the user to workout the answer in his/her head and enter the answer into Excel. The only problem is, of course, that there is nothing stopping a user from simply typing a formula to obtain the answer! I have tried all data validation, apart from Custom which I can't figure out. Is there a way to prevent a user entering a formula on a worksheet?
i have made a document for other users to use. how can i limit the paste function to ONLY ALLOW PASTE SPECIAL-- i wish to aviod damage to formatting and formula etc
deletes a row if it finds a specified value in a specified column (in this instance, "NB" in column E). However, it is very slow and some end users are complaining about the amount of time it takes to run. Here's what I'm using at the moment:
I am contracting an excel template and need to stop users saving the file using “Save” or “Save As”. I am able to do this by using the private sub below: Private Sub Workbook_BeforeClose(Cancel As Boolean) Me.Saved = True End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True End Sub
I also understand that by switching to “Designer” mode you can save the file and once re opened designer mode will be switched off. What I need is to allow saving using a macro, is there a code that I can insert in the macro to switch designer mode?
