VBA trim and clean quick

Y

YounesB3

Board Regular
Joined
Mar 28, 2012
Messages
148
Hello, I'm trying to clean and trim a specific range in VBA and I'm having errors. My problem is probably while setting my range, but I'm not sure why.

All my variables are defined earlier in the code:

VBA Code: 
Private LastrowMaster As Long, LastrowCT As Long
Private WS1 As String
Private MainWB As Workbook
Private rng As Range, Area As Range

Set rng = MainWB.Worksheets(WS1).Range("A" & LastrowCT & ":BP" & LastrowMaster)

'If I use this, I get an Object required error
For Each Area In rng.Areas
    Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
Next Area

'If I use this, I get a Type Mismatch error
For Each cell In rng.Cells
    cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
Next cell

What am I doing wrong?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
In the code you have posted you haven't assigned any values to LastrowCT or LastrowMaster (although you are trying to declare the type, which you would normally do before you assigned the values)
YounesB3 said:
All my variables are defined earlier in the code:
Click to expand...
As per the above, please post your full code so we can see exactly what we are looking at rather than guessing.
 
Upvote 0
Here, I fixed it:

VBA Code: 
    Private WS1 As String
    Private MainWB As Workbook
    Private rng As Range, Area As Range

    Set MainWB = ThisWorkbook
    WS1 = "ABC"
    Set rng = MainWB.Worksheets(WS1).Range("A3000:BP3020")

    'If I use this, I get an Object required error

    For Each Area In rng.Areas
        Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
    Next Area

    'If I use this, I get a Type Mismatch error
    For Each cell In rng.Cells
        cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
    Next cell

What am I doing wrong?
 
Upvote 0
Try it like the code below (Run the sub TEST)

VBA Code: 
Option Explicit

    Private WS1 As String
    Private MainWB As Workbook
    Private rng As Range, cell As Range
Sub TEST()
    Set MainWB = ThisWorkbook
    WS1 = "ABC"
    Set rng = MainWB.Worksheets(WS1).Range("A3000:BP3020")

   
    For Each cell In rng.Cells
        cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
    Next cell
End Sub
 
Upvote 0
MARK858 said:
Try it like the code below (Run the sub TEST)

VBA Code: 
Option Explicit

    Private WS1 As String
    Private MainWB As Workbook
    Private rng As Range, cell As Range
Sub TEST()
    Set MainWB = ThisWorkbook
    WS1 = "ABC"
    Set rng = MainWB.Worksheets(WS1).Range("A3000:BP3020")

  
    For Each cell In rng.Cells
        cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
    Next cell
End Sub
Click to expand...
Thanks for the reply! I get an error code that cell is not defined but it is. I have 2 other variables declared as range that work fine, but they use the argument "Set" before.

Like the rng one.

Is cell and Area not supposed to be a range data type?

Still confused. ;p
 
Upvote 0
I don't get the cell is not defined error with the code that I posted, I do with your original code as you hadn't declared it in that code
 
Upvote 0
I found the same as Mark regarding the cell not being declared.

I am not sure why you are using Area since your example only has one Area but I did find it runs against the activesheet which may not be ABC.
Try changing this line to what I have here:
VBA Code: 
Area.Value = Evaluate("IF(ROW(" & Area.Address(1, 1, xlA1, 1) & "),CLEAN(TRIM(" & Area.Address(1, 1, xlA1, 1) & ")))")
 
Upvote 0
This is so weird. They are both defined. I didn't want to post my full code for simplification, but here it is. I still get the message that Area and Cell are not defined (when I remove the comments for either). I don't get it.

Note: I redacted some strings with the mention [Confidential]:

VBA Code: 
Option Explicit

Private LastrowMaster As Long, LastrowInput As Long, returnCount As Long, LastrowCT As Long, i As Long, j As Long
Private YearR As String, Period As String, MonthR As String, InputName As String, AMonth As String
Private WS1 As String, WS2 As String, WS3 As String, WS4 As String, Path As String, OPath As String, Filename As String, Plant As String
Private MainWB As Workbook, InputWB As Workbook, OutputWB As Workbook
Private Sht As Worksheet
Private myrange As Range, rng As Range, cell As Range, Area As Range
Private FSO As New FileSystemObject
Private C1 As Long, C2 As Long

Sub InvLedgerCharts()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.AskToUpdateLinks = False

Set MainWB = ThisWorkbook

YearR = Year(MainWB.Worksheets("Procedure").Range("C3"))
MonthR = Format(MainWB.Worksheets("Procedure").Range("C3"), "MMMM")
AMonth = Format(MainWB.Worksheets("Procedure").Range("C3").Value + 1, "MM""/""dd""/""yyyy")
Period = Format(month(MainWB.Worksheets("Procedure").Range("C3")), "00")
InputName = YearR & Period & " - INV (K2gen).xlsx"
WS1 = "InventoryLedger"
WS2 = "Pivot"
WS3 = "Aging"
WS4 = "Summary"
Path = "[Confidential]\0Input\" 'Path where the files are
OPath = "[Confidential]\0Output\" 'Path where the files are put when done
Filename = Path & InputName

Workbooks.Open Filename

Set InputWB = ActiveWorkbook
Set Sht = ActiveWorkbook.ActiveSheet

LastrowInput = Sht.Range("A" & Rows.Count).End(xlUp).Row

If LastrowInput < 500 Then
    MsgBox InputName & " file's not complete. There is less then 500 records. There should be more. Please validate and rerun macro.'", vbExclamation, "[Confidential]"
    InputWB.Close SaveChanges:=False
    Exit Sub
End If

LastrowMaster = MainWB.Worksheets(WS1).Range("A" & Rows.Count).End(xlUp).Row
MainWB.Worksheets(WS1).Rows("7:" & LastrowMaster).EntireRow.Delete

MainWB.Worksheets(WS1).Range("C6:BI" & LastrowInput).Value = Sht.Range("A6:BG" & LastrowInput).Value
MainWB.Worksheets(WS1).Range("A6:B" & LastrowInput).Formula = MainWB.Worksheets(WS1).Range("A6:B6").Formula
MainWB.Worksheets(WS1).Range("BJ6:BP" & LastrowInput).Formula = MainWB.Worksheets(WS1).Range("BJ6:BP6").Formula
LastrowMaster = MainWB.Worksheets(WS1).Range("A" & Rows.Count).End(xlUp).Row
InputWB.Close SaveChanges:=False

Filename = Dir(Path & "[Confidential]" & "*.xlsx")

Do While Filename <> ""
    Workbooks.Open Path & Filename
    Plant = ActiveWorkbook.ActiveSheet.Range("D2").Value
    Set InputWB = ActiveWorkbook
    Set Sht = ActiveWorkbook.ActiveSheet
    LastrowInput = Sht.Range("A" & Rows.Count).End(xlUp).Row
    With Sht.Range("A1:AF" & LastrowInput)
        .AutoFilter Field:=4, Criteria1:="<>" & Plant
        .AutoFilter Field:=6, Criteria1:="<>[Confidential]"
        .AutoFilter Field:=14, Operator:=xlFilterValues, Criteria2:=Array(1, AMonth)
    End With
    Set myrange = Sht.Range("A1:A" & LastrowInput).SpecialCells(xlCellTypeVisible)
    returnCount = WorksheetFunction.Subtotal(3, myrange) - 1
    If returnCount > 0 Then
        With MainWB.Worksheets(WS1)
            Sht.Range("D2:D" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("C" & LastrowMaster + 1 & ":C" & LastrowMaster + returnCount)
            .Range("E" & LastrowMaster + 1 & ":E" & LastrowMaster + returnCount).Value = Plant
            Sht.Range("Z2:Z" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("F" & LastrowMaster + 1 & ":F" & LastrowMaster + returnCount)
            Sht.Range("AA2:AA" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("G" & LastrowMaster + 1 & ":G" & LastrowMaster + returnCount)
            Sht.Range("J2:J" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("H" & LastrowMaster + 1 & ":H" & LastrowMaster + returnCount)
            Sht.Range("K2:K" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("I" & LastrowMaster + 1 & ":I" & LastrowMaster + returnCount)
            Sht.Range("H2:H" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("J" & LastrowMaster + 1 & ":J" & LastrowMaster + returnCount)
            Sht.Range("I2:I" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("K" & LastrowMaster + 1 & ":K" & LastrowMaster + returnCount)
            Sht.Range("U2:U" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("L" & LastrowMaster + 1 & ":L" & LastrowMaster + returnCount)
            Sht.Range("W2:W" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("M" & LastrowMaster + 1 & ":M" & LastrowMaster + returnCount)
            Sht.Range("N2:N" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("N" & LastrowMaster + 1 & ":N" & LastrowMaster + returnCount)
            Sht.Range("R2:R" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("T" & LastrowMaster + 1 & ":T" & LastrowMaster + returnCount)
            Sht.Range("X2:X" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("U" & LastrowMaster + 1 & ":U" & LastrowMaster + returnCount)
            Sht.Range("Y2:Y" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("V" & LastrowMaster + 1 & ":V" & LastrowMaster + returnCount)
            Sht.Range("P2:P" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("W" & LastrowMaster + 1 & ":W" & LastrowMaster + returnCount)
            Sht.Range("M2:M" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("Y" & LastrowMaster + 1 & ":Y" & LastrowMaster + returnCount)
            Sht.Range("L2:L" & LastrowInput).SpecialCells(xlCellTypeVisible).Copy Destination:=.Range("Z" & LastrowMaster + 1 & ":Z" & LastrowMaster + returnCount)
            .Rows(LastrowMaster).Copy
            .Range("A" & LastrowMaster + 1 & ":BN" & LastrowMaster + returnCount).PasteSpecial Paste:=xlPasteFormats
            .Range("A" & LastrowMaster + 1 & ":BN" & LastrowMaster + returnCount).Interior.Color = 65535
        End With
    LastrowCT = MainWB.Worksheets(WS1).Range("A" & Rows.Count).End(xlUp).Row
    LastrowMaster = MainWB.Worksheets(WS1).Range("C" & Rows.Count).End(xlUp).Row
    End If
    InputWB.SaveAs OPath & YearR & Period & " - [Confidential] - " & Plant & ".xlsx"
    InputWB.Close
    If Dir(Path & Filename) <> "" Then Kill Path & Filename
    Filename = Dir(Path & "[Confidential]" & "*.xlsx")
Loop

Set rng = MainWB.Worksheets(WS1).Range("A" & LastrowCT & ":BP" & LastrowMaster)
'.SpecialCells(xlCellTypeConstants)

For Each Area In rng.Areas
   Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
Next Area

'For Each cell In rng.Cells
'    cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
'Next cell

MainWB.Worksheets(WS1).Range("A6:B" & LastrowMaster).Formula = MainWB.Worksheets(WS1).Range("A6:B6").Formula
MainWB.Worksheets(WS1).Range("BJ6:BP" & LastrowMaster).Formula = MainWB.Worksheets(WS1).Range("BJ6:BP6").Formula

MainWB.SlicerCaches("[Confidential]").ClearManualFilter
MainWB.SlicerCaches("[Confidential]").ClearManualFilter
MainWB.Worksheets(WS2).PivotTables("Pivot3").ChangePivotCache MainWB.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MainWB.Worksheets(WS1).Range("A5", "BN" & LastrowMaster))
MainWB.Worksheets(WS2).PivotTables("Pivot3").PivotCache.Refresh
MainWB.Worksheets(WS3).PivotTables("Pivot1").ChangePivotCache MainWB.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MainWB.Worksheets(WS1).Range("A5", "BN" & LastrowMaster))
MainWB.Worksheets(WS3).PivotTables("Pivot1").PivotCache.Refresh

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.AskToUpdateLinks = True

MsgBox "All done! The procedure can continue.", vbOKOnly, "Success!"

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Trim/Clean Rows faster in VBA
Replies
3
Views
723
mnh
M
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
351
tixarah
T
I
  • Question Question
Unable to run VBA code
Replies
6
Views
165
RoryA
RoryA
excelbytes
  • Solved
Looping Procedure In VBA Code
2 3
Replies
24
Views
478
excelbytes
excelbytes
D
  • Question Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
1K
diversification
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top