1

I have growing data files and in a certain column is not fill out. I have the code to fill the data, though I want to make a button to call userform for user to fill in the range so that the code works within the range provided by the user(since data is adjusted by the user itself). The code to fill the blank cell is;

Sub fillblank()
Dim range As Range
Dim cell As Range
Dim value As String
Set range = Sheets("Sheet1").Range("E4:E15")
For Each cell In range
 If Trim(cell.Value) <> "" Then
   value = cell.Value
 Else
   cell.Value = value
 End if
Next cell
End Sub

I would like the user to enter the range (E4:E15) in userform. How to do the userform if its dependent only range? Thanks stackoverflow.com.

Improve this question

3 Answers 3

Reset to default
1

Put a text box in userform and name it txtRng. Then declare a variable named MyRng as String. Assign that text box value to MyRng variable and use this variable as argument of range like...

Set range = Sheets("Sheet1").Range(MyRng)

So, full code will be like as below

Sub fillblank()
Dim range As range
Dim cell As range
Dim value As String
Dim MyRng As String

    MyRng = UserForm1.txtRng 'Use your form name here
    Set range = Sheets("Sheet1").range(MyRng)

    For Each cell In range
     If Trim(cell.value) <> "" Then
       value = cell.value
     Else
       cell.value = value
     End If
    Next cell

End Sub

I also suggest you to not use range, value as variable because some of these reserve keyword. It may cause misbehave of output.

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

14 Comments

I just want to ask how to write the range in userform? would it be like E2:E5 or R2C5:R5C5? or else?
Just put E2:E5 in text box.
it do nothing when I enter value
Check the file. Download Link
Open the vba code window (Press ALT+F11 to open code window). Double click on user form in left side pane (VBA Project window). Then double click on command button (OK button). You should see the codes behind the button click event.
|
1

You could use the InputBox() method and have the user select a range:

Sub fillblank()
    Dim myRange As Range
    Dim cell As Range
    Dim myValue As String

    Set myRange = Application.InputBox( prompt:="Select a range", Type:=8) 
    For Each cell In myRange
        If Trim(cell.Value) <> "" Then
            myValue = cell.Value
        Else
            cell.Value = myValue
        End if
    Next
End Sub

or you could add a RefEdit control to your userform and process it

Sub fillblank()
    Dim cell As range
    Dim myValue As String
    For Each cell In range(Me.RefEdit1.Text)
        If Trim(cell.value) <> "" Then
            myValue = cell.value
        Else
            cell.value = myValue
        End If
    Next cell
End Sub

In this case, since the user could input an invalid range, you may want to add a validation function (named GetRange in my following example)

Sub fillblank()
    Dim myRange As range
    If Not GetRange(Me.RefEdit1.Text, myRange) Then
        MsgBox "Select a valid range "
        Me.RefEdit1.SetFocus
        Exit Sub
    End If

    Dim cell As range
    Dim myValue As String
    For Each cell In myRange
        If Trim(cell.value) <> "" Then
            myValue = cell.value
        Else
            cell.value = myValue
        End If
    Next cell
End Sub

Function GetRange(RefEditText As String, myRange As range) As Boolean
    On Error Resume Next
    Set myRange = range(RefEditText)
    On Error GoTo 0
    GetRange = Not myRange Is Nothing
End Function

finally, here is an alternative method (no loops) to fill blank cells as you're wanting to do:

Sub fillblank()
    With range(Me.RefEdit1.Text).SpecialCells(xlCellTypeBlanks)
        .FormulaR1C1 = "=R[-1]C"
        .value = .value
    End With
End Sub
Improve this answer

2 Comments

what do you mean refedit? I am actually new in vba and just heard from you on the refedit fn. I have try the code and it gave object-defined error.
RefEdit control is a control you find in the userform toolbox, where you can drag controls (like button, textbox, listbox, etc) and drop them onto your userform canvas. I’m assuming your button is on a userform
1

To ask the user for a range you could use InputBox(Type:=8)

The code bellow will accept only one column

If an entire column is selected (A:A) or multiple columns it adjusts to the total rows in UsedRange and first column in selection

Option Explicit

Public Sub FillBlanks()
  Dim selectedCol As Range, itm As Range, lr As Long

  On Error Resume Next
    Set selectedCol = Application.InputBox(Prompt:="Select column:", Type:=8)
  On Error GoTo 0

  If selectedCol Is Nothing Then Exit Sub   'User cancelled selection

  With selectedCol
   .Parent.Activate
   lr = .Parent.UsedRange.Rows.Count
   If .Columns.Count > 1 Then Set selectedCol = .Resize(.Rows.Count, 1)
   If .Rows.Count < 2 Or .Rows.Count > lr Then Set selectedCol = .Resize(lr - .Row + 1, 1)
   selectedCol.Select
  End With

  For Each itm In selectedCol
    If Len(Trim$(itm)) = 0 And itm.Row > 1 Then itm.Value2 = itm.Offset(-1).Value2
  Next
End Sub

Note: It's not recommended to name your variables with special VBA keywords

  • Dim range As Range - Range is the most important object in Excel
  • Dim value As String - Value is the most important property of the Range object
Improve this answer

4 Comments

how to key in the input box? Is it will be E2:E5? or just the number 4?
When the box appears you can select the range on the sheet (with the mouse)
I agree with the note. This is just for me. In the user excel will be different.
I made the update. Let me know if there are any issues. You can also enter E2:E5 directly

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.