CODEWITHSUNDEEP
vbaexcel
Quinten van Alphen
Quinten van Alphen

Reputation: 3

Use Range for a whole column instead of one cell

What I currently have:

    Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsEmpty(Range("G15").Value) = True Then
        If Selection.Count = 1 Then
            If Not Intersect(Target, Range("G15")) Is Nothing Then
                Range("G15").Value = Date
            End If
        End If
    End If
End Sub

I've created this for the following reason: I want to click on a cell on which it's automatically puts in the date of today. Now, if the cell already contains text (doesn't matter what), it doesn't have overrule that input.

What I created works perfectly.. for one cell. I want it to work for the whole column (in this case, the "G" column).

I tried using Range("G1:G1000"), but that didn't work. I've tried using a tutorial which explains how to use multiple ranges (http://www.yogeshguptaonline.com/2009/05/macros-in-excel-selecting-multiple.html) but that didn't work out either.

What am I missing here?

Upvotes: 0

Views: 1402

Answers (3)

user4691433
user4691433

Reputation:

If you might have some non-empty cells in the range, but want to fill all the empty ones, you would need:

Option Explicit

Sub FillEmptyWithDates()

Dim Cell1 As Range
Dim Range1 As Range

Set Range1 = ActiveWorksheet.Columns(7)
For Each Cell1 in Range1
    If IsEmpty(Cell1.Value) = True Then
        Cell1.Value = Date
    End If
Next Cell1

End Sub

I changed your code a little bit because it seemed convoluted for what you seem to be trying to accomplish: fill all empty cells in G with the current date.

This macro no longer triggers every time you select a new cell. You would go to the macros menu, select it, and push the Run button.

Upvotes: 0

99moorem
99moorem

Reputation: 1983

Use below, this works if you select the row in G that you want date added to

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsEmpty(Range("G" & Target.Row).Value) = True Then
        If Selection.Count = 1 Then
            If Not Intersect(Target, Range("G" & Target.Row)) Is Nothing Then
                Range("G" & Target.Row).Value = Date
            End If
        End If
    End If
End Sub

Upvotes: 0

John Coleman
John Coleman

Reputation: 52008

Range("G15").EntireColumn

Might be what you are looking for.

Upvotes: 2

Related Questions