Use User-defined range as input for cell parsing

Question

I'm writing a macro in Excel 2010 in order to remove line breaks in multiple cells of a column. This cells need to be selected by the user. Following this previous post I was able to create an InputBox to let the user select the range but now, I am unable to process the data within the selection.
My previous code without the selection range parsed an entire column with a regexp to find a pattern in the string within the cells and change its contents. I did this with a For i To Rows.Count block of code like this:

For i = 1 To Rows.Count
 
    If Not IsEmpty(Cells(i, 5).Value) Then
       varString = Sheets(ActiveSheet.Name).Cells(i, 5).Text
       Sheets(ActiveSheet.Name).Cells(i,5).Value=objRegExp.Replace(varString, "$1 ")
    End If
Next i

Now I want to replace the static column so I can process only the user range. In order to achieve that I tried this:

Set selection = Application.InputBox(Prompt:= _
            "Please select a range to apply the remove break lines procedure.", _
                Title:="Remove Line Breaks", Type:=8)
                
If selection Is Nothing Then
    Exit Sub
End If

Set RowsNumber = selection.CurrentRegion -> This line gives me an error: "Object required"
Set RowsNumber = RowsNumber.Rows.Count

For i = 1 To RowsNumber
    If Not IsEmpty(Cells(i, 5).Value) Then 
       varString = Sheets(ActiveSheet.Name).Cells(i, 5).Text 
       Sheets(ActiveSheet.Name).Cells(i, 5).Value = objRegExp.Replace(varString, "$1 ") 'Replace pattern found with regular expression in the same line
    End If
Next i

How can I access the cells in the range returned by the InputBox?
I also tried changing RowsNumber with selection.Rows.Count but that way, although it doesn't gives an error, the cells used have blank string within them when I run the debugger. I think this is because I try to access row = 5 when the range could be less, i.e 3 if user just selects 3 cells.
I tried a For Each Next loop but then again, I know not how to access the cells withing the selection range.

try Set RowsNumber = Selection not selection.curentregion. Why could you not do RowNumber = selection.Rows.Count only has one line this way — 99moorem
– 99moorem, Commented Aug 13, 2015 at 14:09
I tried doing it that way @99moorem but it gives me the same error of 'Object required' — Edgar Sampere
– Edgar Sampere, Commented Aug 13, 2015 at 14:15

mielk · Accepted Answer · 2015-08-13 14:12:33Z

0

You can iterate through the cells of a range by using For Each loop.

Below is your code modified. I have changed the name of variable Selection to rng, because Selection is Excel library built-in function and this name should be avoided.

Sub x()
    Dim rng As Excel.Range
    Dim cell As Excel.Range


    Set rng = Application.InputBox(Prompt:= _
                "Please select a range to apply the remove break lines procedure.", _
                    Title:="Remove Line Breaks", Type:=8)

    If rng Is Nothing Then
        Exit Sub
    End If

    For Each cell In rng.Cells
        If Not IsEmpty(cell.Value) Then
           varString = cell.Text
           cell.Value = objRegExp.Replace(varString, "$1 ") 'Replace pattern found with regular expression in the same line
        End If
    Next cell

End Sub

answered Aug 13, 2015 at 14:12

mielk

3,94015 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Edgar Sampere Over a year ago

Ohhh! I get it, so I can access the range as a list of cells directly, as if an array we were talking about. Thanks @mielk

99moorem Over a year ago

I read the OP's code as he was using the selection not trying to set the selection :)

Edgar Sampere Over a year ago

Don't worry. Now something weird is happening or maybe I'm just to new to vba but my function's ready, it does what it has do to only if I call it using a button but if I call it from the formula creator, it executes normally but it doesn't do anything

Collectives™ on Stack Overflow

Use User-defined range as input for cell parsing

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related