Next cell in column

[jamescooper]

If in, for example, column A you want to paste the data in the next blank row even if it isn't at the end of the data set as shown below - how would this be done via VBA?

Apples
"Blank"
Pears
Pears
Pears
Pears
"Blank"

Thanks.

 

[Kenneth Hobson]

Sub Test()
    Dim r As Range
    On Error Resume Next
    Set r = Intersect(Columns("B"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeBlanks)(1)
    If Err.Number <> 0 Then Exit Sub
    Debug.Print r.Address
End Sub

 

[jolivanes]

Sub Maybe_A()
    ActiveSheet.UsedRange.Columns(1).SpecialCells(4).Cells(1).Select    '<---- or .Value = "Look at this ma!"
End Sub

Kenneth was way ahead of me incl error checking.

 

[kweaver]

My simplistic VBA understanding, I used a different approach if the value being inserted is in B1 and the data in column A:

Sub PasteFirstBlank()
    Dim LR As Long, i As Integer
    LR = 1+Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To LR
      If Len(Cells(i, 1)) = 0 Then
      Cells(i, 1) = Range("B1")
      Exit Sub
      Else
      End If
    Next i
End Sub

 

[Kenneth Hobson]

"Empty" value cells means different things to different people. If the column's usedrange has no "blank" cells, then one can likely use this.

Sub Test()
    Dim r As Range
    On Error Resume Next
    Set r = Intersect(Columns("B"), ActiveSheet.UsedRange).SpecialCells(xlCellTypeBlanks)(1)
    If Err.Number <> 0 Then Set r = Cells(Rows.Count, "B").End(xlUp).Offset(1)
    On Error GoTo -1
    MsgBox r.Address
End Sub

 

[jamescooper]

Sorry, it is via a data pull and it is the nextrow below, will this work here too?

Thanks.

Public Sub Data_Pull_Prices()


Dim http As Object, html As New HTMLDocument, topics As Object, titleElem As Object, detailsElem As Object, topic As HTMLHtmlElement
Dim i As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
Dim NextRow As Range


http.Open "GET", "https://www.tesco.com/groceries/en-GB/shop/fresh-food/all?include-children=true&page=1&count=48", False
http.send
html.body.innerHTML = http.responseText


Set topics = html.getElementsByClassName("price-control-wrapper")
Set NextRow = Sheets(1).UsedRange.Columns(2).SpecialCells(4).Cells(2).Value




For Each topic In topics
Set titleElem = topic.getElementsByTagName("div")(0)
Sheets(1).Cells(NextRow, 2).Value = titleElem.getElementsByTagName("span")(0).innerText




Next


End Sub