I have a defined range in my workbook which I have not been able to delete (I think it got there through some malware because I didn't create it!)
The main annoyance is that the name refers to another workbook (to which I don't have access) and to a non-existent range therein (resulting in a #REF! error) - so I get the unwanted Update Links message each time I open the workbook. I have created my own workbook with the same name as the workbook referred to by the name, hoping that I may gain some level of control over this gremlin, but to no avail.
The name is "Flow" immediately followed by a thin-lined empty square (like the ANSI character 042 formatted using Wingdings2 font). It could be a line-feed character.
Whatever it is, I have not been able to delete this range name - either directly through the Insert / Name / Define / Delete commands or by using VBA. Using the menu commands I can select the name, edit it (even add a valid cell reference to the workbook name), and click Add - but that just adds a new name "Flow" without the control character and the original name remains! The original name also remains if I select the name and click Delete.
When I try to add the Richtextbox control, I get this error: "The control could not be created because it is not properly licensed." Since I'm in a corporate setting, I don't think I can get around this (since I don't have access to the computer's registry and several users will be using it). I have figured out a way to present the user with some rich text controls (Bold, Italics, Underline, Font, Color, etc), which will add special characters around the selected text which then is interpreted by a procedure and formats the text in the output destination (HTML document).
However, that tends to clutter up the textbox and makes it somewhat cumbersome for some users to edit the text. I'd prefer to have them see the formatted text in the textbox, if possible. So, is there a way to get some sort of Rich Text Box without the RichTextBox control? It's long shot, I know.
My range is A2:T600 on one single worksheet. In many cells, the first character is a blank space (a mistake of mine when creating the initialize code in the user form into which the info was entered). I only need to do this once ever for this worksheet, but I hate to go through them all by hand. So what I need is to select the range, and IF the first character of a cell is a blank space, delete just that character.
Sub rightval() Dim myrange, mycell As Range Set myrange = Sheets("Sheet1").Range("A1", Range("A65536").End(xlUp)) For Each mycell In myrange If Right(mycell.Value, 1) = "." Then mycell.Value = Left(mycell.Value, Len(mycell.Value) - 3) End If Next mycell End Sub
I'd like to delete the last three values of one cell only if it contains a period.
I'm trying to use Excel 2003 macros for the first time and am very frustrated by it's recorder function. I've used several standalone TSR macro recorders several years ago and they were much easier but certainly not as feature rich.
I have a spreadsheet that I've been using for a couple of years where I have manually entered data. My company has recently started to dump data into an Excel sheet but instead of pure numeric or date values being used they are extracting the values with the ' character starting the value. I want to strip this ' character.
What I do is select the cell I want to edit and invoke the macro. Then press {F2} to edit the contents of the cell, {Home} to move to the left, {Del} to delete the first character - the apostrophe, and {Enter} to complete the edit and move down one cell.
Using the recorder captures the following. Unfortunately it doesn't simply delete the first character, it copies the value of the previous cell to the next cell when I invoke the macro again.
I would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria.
After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases.
None of the functions I have see so far will facilitate this...
I'm using excel 2010. I'm working with columns of values where most of the values are numbers - which is fine, and there are some numbers that have a "p" at the start of them.
e.g. Column has 49, 52.2, p56.7, 34
OK, I want to preserve the 56.7 but I want to delete the "p". I'm thinking I've got a mental block as to how to delete the unwanted "p"s but I can't think of how to do it at the moment! My code goes as follows:
If Left(Cells(1,1),1) = "p" then 'delete the "p" and leave the remaining number in tact End if
I am trying to read a text file into a variable with VBA. The files I try to read usually have a bunch of unrecognized characters in them and I can't seem to read through them. I am looking for some code that will delete all unrecognized characters until it finds a string I specify.
Example of file
ΚW ¾J Y D Y ³F Y ZE Y ¨B Z ˜6 Z GOOD DATA
My code works fine if I manually delete all this stuff before GOOD DATA, but wont work if I dont. My "responseposition" is always 0 unless I delete all the nonsense.
I have one column with many numbers. Some have one dash and some have two.
Example: 123-123456-65 012-789546-1 98B12354-889
Is there a way that I can remove all characters after the last – (dash) in the number? Example:
If number is 123-123456-65 Then 123-123456
If number is 98B12354-889 Then 98B12354
If someone could just lead me in a direction, I might be able to figure it out. However, my code is elementary and most of the time, I record macros and the play with the code until it does what I want.
Following the tips doesn't allow to remove a character.
I exported email addresses from Outlook and they have the following character ' in front of the email address.
Using Excel 2007.. it says :"check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected worksheet. Excel cannot replace data on a protected worksheet"
So I made a new file, and copied and pasted the cells into the new spreadsheet.. same message.
when open xl app my addin is installing, but when excution gets to delete the my addin an error appears tells :invalid procedure call or argument.
Sub workbook_open On Error Resume Next 'Just in case '*Delete existing Super Menu if it was left. Application. CommandBars("Worksheet Menu Bar").Controls("Accountant Menu").Delete On Error Goto 0
I have a custom email creation template I am merging with another version. The problem I am having is wrapping my head around not only selecting a range that is offset from ActiveCell (column 6-9) but seeing if there is an "x" in that range which is normally blank. My previous attempts identify the "x" but adds the text every time it is found. (Each column is a flag for an email bullet and they can have all four bullets in the email where I only want the text included ONCE if they have ANY bullets included). I use the range because I do not want the text included if none of the bullets are used.
Teh StandHTML then gets used in the body of the email like other HTML items I use
The email is generated using the ActiveCell.Offset to insert special text, emails and routing and has weathered alot of changes over time.
Dim Myrange As Range Myrange = Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 9)).Select If Myrange = "x" Then StandHTML = StandHTML & "Important Text" End If
I want to delete a picture from an image control in a worksheet when pressing a button. So my code is:
Private Sub CommandButton1_Click() Sheets("Sheet1").Image1.Picture = LoadPicture("") End Sub
Well, the problem is that this code only works when I create it. If I save and close the excel file when I open it again and I press the CommandButton1 I get the following error: Run-time error '-2147417848(80010108)' Method "Picture of object "IImage" failed.
I am not the best at this, but we recorded a macro and we want to delete whole rows after we 'control' find something from a specific column. WHen we recorded our macro, it finds the first instance and we hightlight the whole row and 'control' 'shift' 'end' and delete all of the rows. We did this because we sorted and made sure the information we wanted to delete was at the bottom of the worksheet. we realized that each day the spreadsheet we pull from an ip address gets bigger and the row that we started from to delete starts on a different row each day. How do we get all the information to delete everyday, even when the row that Owned starts on changes? ...
Hi all im having a tough time trying to figure out a way to select a range (which will be the entire row )based on a condition that will be data selected from a combobox and display that range in a userform with spreadsheet contol 9
what i have so far is a worksheet with all the data. i have a combobox on a userform that is prefilled with data from a specific column
every thing is working except when i select the entire row how do i change that to a range that i can use in the spreadsheet control 9 that is added to the userform.
I would like to define the range of cells in the sum with a number in another cell. Say I want to sum the past 10 years, I would like the sum definition to be controlled by another cell that includes the number 10.
If I want B1 to be the sum of A1:A10, B2 to be the sum of A2:A11, etc., I would like to be able to control the range of the sum with say, C1, which in this case would contain the number 10.
This would allow me to easily change the range of data I am summing: if I changed C2 to 15, B2 would give me the sum of A1:A15 for example.
I have built a user form that amongst it's other functions displays the results from three cells on one worksheet.
The values in these cells need to be pasted in three cells on a different worksheet (cells are selected by user using a "RefEdit" field on the form.
The VBA help files offer a single example of the paste special method (I've proven I can paste to the celss but of course that pastes the formula rather than the value!) that shows it operating on a single worksheet as follows:
With Worksheets("Sheet1") .Range("C1:C5").Copy .Range("D1:D5").PasteSpecial _ Operation:=xlPasteSpecialOperationAdd End With
So, simple question...
How can I use the Paste Special method to paste the values of the cells in cells specified by the user?
I have a workbook with 20 sheets, including a control sheet 'Validation Table'. By setting the required Background/Font in a cell named hdrDefault on sheet VT, I want to click a button on VT to invoke a macro to visit each sheet and set the named Header range, hdrxxx, to the same Background/Font. Originally had the code in a Private Sub linked to a button event, but this can't break out of the worksheet so have moved the Sub into a Module, and calling from the click event:
I have sheet where I need to restrict access to a select group of individuals. I have a formula in a select case statement that runs from the workbook_open event and have hard coded the names of the individuals who can access the file. If they are not on the list, they are prompted for a password. This works OK, but I have sheet that is hidden and want to maintain the list of users there and update the code to loop through the values in a named range. I have a snippet of the current code below.
Code: Select Case function_name Case Is = "user1" Run "enable_access_code" Case Is = "user2" Run "enable_access_code" Case Is = "user3" Run "enable_access_code" Case Else ' User not authorized or opened while not on company network ' Prompt for project password form_Password.Show End Select
I'm trying to set up a userform with a refEdit box that allows the user to select a cell range. I then need to assign that cell range to a range variable so the macro can use it. I'm having a problem reconciling the assigned string variable that refEdit returns and the range variable that I need.
So what I'm trying to say is that I can assign the range fine through the Userform, and I can see through the debugging screen that mySampleVariable (dim as a variant) holds a range value of 'Access Dump'!$A$8 (the correct range), but when I try to
Dim myRange As Range Set myRange = mySampleVariable
The code bugs out because it apparently can't assign a string value to a range variable.
the macro simply copies a master sheet, then re-organizes/re-sorts the raw data in the new sheet. At the end, I'm trying to set a print range to 1 page wide and inifinity pages long, figure out where the end of the fourth page is, then reset the print range to accommodate four pages. In case it matters, the macro starts with a form that allows the user to input a company ticker and name used in output.
The problem is that when I use the debugging step function (very tedious), it works perfectly. But when I run it normally, it locks up for about 30 seconds when setting the print range to 1 page wide, and cuts off the print range vertically at just over 2 pages tall. Brief explanation of a couple variables:
ColLetter is a function as follows: Function ColLetter(ColNumber As Integer) As String ColLetter = Left(Cells(1, ColNumber).Address(False, False), 1 - (ColNumber > 26)) End Function ActiveSheet.PageSetup.PrintArea = "$A$1:$" & ColLetter(j + 3) & a + 7 .............................
I want a control to select a range in a userform I'm creating.
I wonder if we can use the same type of control the wizards use, where you click on the right and choose the range? Alternatively, what's the best option?
