1. D

    My VBA is changing a formula I"m trying to code in, and it's breaking my macro...

    I have a macro that formats a set of data. Part of it is that I this line: ActiveCell.FormulaR1C1 = "=MID(B4,FIND(" - ",B4)+1,LEN(B4))" The problem is, I don't want a space before and after the hyphen. When I take it out and save, it puts the spaces back in and it's driving me insane. How can...
  2. S

    Format/field width specifier for UserForm TextBox

    I have a TextBox on a UserForm that displays the results of calculations done inside the UserForm's code. I build up a string using the concatenate operator along with spaces (" ") and the values. When all calculations are finished, I print a report similar to the following: input output...
  3. B

    replacing tabs with spaces

    is there a way I can replace the use of a tab with just a single space bar? I dumped down a ton of data that separates names and numbers using a tab instead of just a single space. I tried the find replace function putting in five spaces with one space but that doesn't work
  4. J

    table shows lots of unexepected spaces when copies from Excel to Word

    Hi, everyone I'm writing a code to copy tables from EXCEL to WORD,and in WORD the code can adjust table's columnswidth. I find an interesting thing in this process, that is when a table copies from EXCEL to WORD, it shows a lot of spaces in it's cells where the cell has number, but when the...
  5. D

    vbscript .find ignore spaces

    I have a script that searches phone numbers Set rngFind = .Find(What:=Trim(txtLookup.Text), LookIn:=xlValues, LookAt:=xlPart) Some of the numbers have spaces and some are just numbers. eg in the database a phone number may show up as "0218888888" or "021 888 888" Is there a way so when my...
  6. W

    on error

    I am using this script in a macro and it runs fine but if it runs a second time and nothing has changed it geves me an error. How can I protect against that? ' pad Spaces ActiveSheet.UsedRange.SpecialCells(xlBlanks).Value = " " <strike></strike>
  7. S

    Preserve Bold/Unbolded Text

    Hi, I'm helping a friend with a mass of text data that has been imported into Excel. The data is textual, consisting of bold text followed by unbolded, and forum help has shown me how to split bold from unbold. However, it now seems there are issues with the imported data, namely leading and...
  8. S

    Leading Spaces - something odd ?

    Hi, I have some code from this site that removes leading spaces from most cells in my data, but not all. What sort of thing could cause these 'unknown' spaces and how do i deal with them please ? Here's the code: Sub RemoveLeadingTrailingSpaces() ' hiker95, 02/26/2016, ME9242394 With...
  9. G

    field with spaces that need to be removed

    Good day guys and girls I have an issue and need to find a way to resolve it there is a column that has an amount and in that field, the number has 6 spaces and cant removed with formula I have to do it manually $ 1,632.00 I try to replace, =substittute(h2," ","")...
  10. K

    VBA dynamic dropdown list

    See link for the file: There is VBA code for dynamic dropdown in sheet "Entry", you can test it out and see how it works. I have two issues with it which I can't resolve: 1)The drilldown has empty spaces when you drill...
  11. J

    Macro Loop through cells in column

    Have a column C with a lot of data. Many of the cells contain spaces in the end. I want to trim each cell by using a macro looping through each cell. I want to practice looping. Is it possible to write a code which loops through each cell and removes the space(s) in each cell. Thanks.
  12. W

    Insert a space into phone numbers

    Hi, I am new to VBA and trying to get to grips with it. I have a large list of phone numbers that are in different formats i.e. 01234 456 789, or 01234 456789 ect. I have managed to clear all spaces out to leave continuous numbers, now I want to insert a space after 5th digit of the area code...
  13. C

    How to remove the spaces before and after the numbers

    How do I remove the spaces before the numbers and after in excel. I tried find and replace and it does not work. I also tried trim still no use. <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> 300.00 440.00 1,694.11 2,909.54 5,400.00...
  14. J

    Pivot table subtotals

    Hello, I got rid of subtotals in this pivot table, but how can I get those hierarchy titles and empty spaces to go away?
  15. E

    Sheet name without spaces have quotes around them- used VBA to create sheets and name based on cell value

    Hi all, I have a code that adds in n number of new worksheets and names it based on cell value. Eventually, I have a summary sheet that will perform lookups to respective sheets using indirect. The newly created sheet names do not have any spaces in them, but for some reason the references...
  16. P

    Regex: match all spaces except for those after an exact match of a single word or two words

    Hi I am trying to match all white spaces in a sentence, except for those after either a match on a particular word, or that particular word followed by another particular word. I'm trying to use either lookahead or lookbehind. I can match the space immediately preceding the particular word...
  17. M

    Remove Spaces

    Hi All, My data is supposed to look a certain way ( see below) I need to remove spaces between D, the ID # (for example DXH110817) and the last name Doe. I'm trying to find an efficient fast way of doing this since I have about 50 records in my data. Any suggestions? So right now, it...
  18. B

    Creating a block of string that centers itself with spaces

    I am trying to figure out some way to take a string and make sure its a certain number of characters (I would say width but that would be much more involved given characters and point sizes are so confusing!!!) and center it via spaces. For instance, if I have a string "cat" but I want it to...
  19. S

    VBA to remove spaces from specific part number string

    Hello, I am trying to create a macro that will remove spaces from a column, IF it starts with "3 464 xxx xxx". Our vendor has spaces in their part numbers, but our system does not use them. Externally, we have to keep the spaces, but for internal use I need to remove those spaces. I have...
  20. J

    Using paths and sheet names with spaces in vba.

    Hi all, I'm trying to use this argument (arg): 'T:\SUPERVISOR REPORTS\2018\[May 2018.xlsx]10-D!R7C2 like this: GetValue = ExecuteExcel4Macro(arg) BTW, arg is created like this: arg = "'" & path & "[" & file & "]" & sheet & "!" & Range(ref).Range("A1").Address(, , xlR1C1) (it was found...

Some videos you may like

This Week's Hot Topics