Results 1 to 13 of 13

VBA Trim Leading Spaces

Threaded View

  1. #1
    Registered User
    Join Date
    01-30-2020
    Location
    UK
    MS-Off Ver
    2013
    Posts
    82

    VBA Trim Leading Spaces

    Hi All

    I have source data that contains leading spaces in some blank cells. I've adapted the below to try and remove them using an array, but instead it is wiping column A. Could you help tweak it please?

    Many thanks!

    Sub Trim_Cells_Array_Method()
    
    Dim ArrIN() As Variant, ArrOut() As Variant
    Dim lRows As Long, lCols As Long
    Dim i As Long, j As Long
    
    Set rng1 = ThisWorkbook.Sheets("OP").Range("A10:G1500")
    ArrIN = rng1.Value
    
    LR = rng1.Rows.Count
    LC = rng1.Columns.Count
    
      ReDim ArrIN(1 To LR, 1 To LC)
      ReDim ArrOut(1 To LR, 1 To LC)
    
      For j = 1 To LC
      For i = 1 To LR
          ArrOut(i, j) = Trim(ArrIN(i, j))
      Next i
      Next j
    
      rng1.Value = ArrOut
    
    End Sub
    Last edited by warp765; 04-16-2020 at 11:42 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] trim does not remove leading spaces?
    By inventorgeorge in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-06-2024, 09:38 PM
  2. [SOLVED] trim to match two sets of input-trim spaces around a character
    By nigelog in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-21-2017, 12:33 PM
  3. [SOLVED] TRIM leading character
    By lemonap618 in forum Excel General
    Replies: 3
    Last Post: 01-20-2016, 11:01 PM
  4. TRIM function and leading spaces
    By nsv in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-01-2014, 10:32 PM
  5. Delete Trailing Spaces without Removing leading zeros WITHOUT TRIM
    By Zach51215 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-20-2013, 04:24 PM
  6. trim leading spaces
    By captain bob in forum Excel General
    Replies: 2
    Last Post: 08-03-2006, 10:00 AM
  7. [SOLVED] trim leading spaces
    By [email protected] in forum Excel General
    Replies: 10
    Last Post: 05-11-2006, 04:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1