PDA

View Full Version : How do I set/get a range from a certain cell to the last row in that same column



new2code
11-01-2016, 07:00 AM
Hello all,
I'm sure this is an easy one, but for some reason I cannot seem to have my grey cells working to solve it!
I have a list, and I'd like to find the column with a specific header/title, and then stuff some value into the cells of the filtered/visible rows of that column only (and only in the data range).

I am able to find the column using the second line of code below, but obviously the entire column (all the way down to the sheets' very last row) gets stuff with the value [since I'm using 'EntireColumn'])

What do I need to do in order to get the range from the cell just below the found title/header (i.e. row # 2, or the first data row), down to the last row of data in the same column?



Dim rngColumnToUpdate As Range
Set rngColumnToUpdate = .Rows(1).Find("My Favourite Column").EntireColumn


I've tried the following code as well...but only the topmost cell of the found column is being updated with the specified value, which in turn is overwriting the heading/title. No other cell is being written to.



Dim rngColumnToUpdate As Range
Set rngColumnToUpdate = .Rows(1).Find("My Favourite Column")
Set rngColumnToUpdate = .Range(.Cells(2, rngColumnToUpdate.Column), .Cells(Rows.Count, rngColumnToUpdate.Column).End(xlUp)).SpecialCells(xlCellTypeVisible)
rngColumnToUpdate.Value = "BlahBlahBlah"


Thanks

Kenneth Hobs
11-01-2016, 04:53 PM
Intersect() and Union() is how I did it here. Pick the Main method that you like best.

Sub Main()
Dim r As Range, rc As Long

With ActiveSheet
Set r = .Rows(1).Find("My Favourite Column")
If r Is Nothing Then
MsgBox "COlumn, My Favourite Column, was not found.", _
vbCritical, "Macro Ending"
Exit Sub
End If

rc = r.Column

Set r = Intersect(.UsedRange, _
.Columns(rc).SpecialCells(xlCellTypeVisible))

If r.Cells.Count = 1 Then
MsgBox "No data found to change.", vbCritical, "Macro Ending"
Exit Sub
End If

Set r = Intersect(r, .Range(.Cells(2, rc), _
.Cells(.Rows.Count, rc).End(xlUp)))

r.Value = "Main"
End With
End Sub


Sub Main2()
Dim r As Range, rc As Long

Set r = .Rows(1).Find("My Favourite Column")
If r Is Nothing Then
MsgBox "COlumn, My Favourite Column, was not found.", _
vbCritical, "Macro Ending"
Exit Sub
End If

rc = r.Column

With ActiveSheet
Set r = Intersect(.UsedRange, _
.Columns(rc).SpecialCells(xlCellTypeVisible))

If r.Cells.Count = 1 Then
MsgBox "No data found to change.", vbCritical, "Macro Ending"
Exit Sub
End If

Set r = StripFirstRow(r)

r.Value = "Main2"
End With
End Sub


Function StripFirstRow(aRange As Range) As Range
Dim i As Long, j As Long, r As Range, z As Long, idx As Long
For i = 1 To aRange.Areas.Count
For j = 1 To aRange.Areas(i).Rows.Count
z = z + 1
If z = 1 Then GoTo NextJ
If r Is Nothing Then
Set r = aRange.Areas(i).Rows(j)
Else
Set r = Union(r, aRange.Areas(i).Rows(j))
End If
NextJ:
Next j
Next i
Set StripFirstRow = r
End Function

mikerickson
11-01-2016, 06:15 PM
Dim rngColumnToUpdate As Range
Set rngColumnToUpdate = .Rows(1).Find("My Favourite Column")

With rngColumnToUpdate.EntireColumn
Range(.Cells(2, 1), .Cells(.Rows.Count,1).End(xlUp)).Value = "someValue"
End With