Proper Formatting - VBA - Loop Issue

[SteveOranjin]

Hello,

I have written the below code. I think it works, but it takes forever to wheel through the code. Can someone take a quick little peak and give me a shove in the right direction?

Sub Proper_Formatting()
'We must first of all, declare objects in a range.
Dim rng As Range, cell As Range


'We're going to prevent the screen from running its accustomed updates while it is performing the procedures.


Application.ScreenUpdating = False
        'We are selecting the range of data that we want to work with
        Sheets("Data Sheet").Select
        
        'We want to select the cells within the sheet
        Cells.Select
        
        'We are defining one of the variables to accord to the selection we just made.
        Set rng = Selection
        
        For Each cell In rng
            cell.Value = WorksheetFunction.Proper(cell.Value)
        Next cell
Application.ScreenUpdating = True


End Sub

 

[Fluff]

You are looking at the entire sheet, ie 1048576 rows * 16384 columns =17179869184 cells

<tbody>
</tbody>

try usedrange

 

[Rick Rothstein]

Here is a more compact, non-looping macro which should be quite "snappy" that you can try...

Sub ReplaceLFwithCommaSpaceAfterRemovingTrailingComma()
  With ActiveSheet.UsedRange
    .Value = Evaluate("IF({1},PROPER(" & .Address & "))")
  End With
End Sub

 

[SteveOranjin]

So the problem with using "UsedRange" is that I continually use one sheet. I just dump something into it, and then when I'm done I transfer it to a new sheet with a macro. There seems to be some kind of 'residue' that remains in the cells in the sheet after I clear it. I was having problems with some of my formulas that were providing me information about the data, so I set up the clear macro to 'clear' the data rather than to delete the existing data.

So I thought I would create a named range that I could then run the macro on. This however, does not appear to be working either.

I'm still pretty green at writing my own VBA, but it seems to me that this should work. the rng variable corresponds to the selection. Is the selection of the range in question somehow not being passed into the variable?

Hope you are well,

Steve

Sub Proper_Formatting()'We must first of all, declare objects in a range.
Dim rng As Range, cell As Range


'We're going to prevent the screen from running its accustomed updates while it is performing the procedures.


Application.ScreenUpdating = False


        'We are now selecting the sheet that we want to change the casing on
        Range("Data_Sheet_Data").Activate
        Range("Data_Sheet_Data").Select
        'We are now making a selection. That selection, is the used cells within the worksheet 'data sheet'
        
        'We are defining one of the variables to accord to the selection we just made.
        Set rng = Selection
        
        For Each cell In rng
            cell.Value = WorksheetFunction.Proper(cell.Value)
        Next cell
Application.ScreenUpdating = True


End Sub

 

[SteveOranjin]

btw - if using is a named range is not a good idea for this, than I am open to alternatives. I know I'm going to have to at least set up my clear macro to enter a value into cell A1 after to preserve the named range (right?)

 

[Fluff]

Have you tried Rick's suggestion?

 

[SteveOranjin]

Mine was working. But his is MUCH better.