CODEWITHSUNDEEP
excelvba
MrJaffaCake
MrJaffaCake

Reputation: 13

Skip rows with empty cells

I'm working on automating orders over WhatsApp with an Excel Sheet.

My programming experience is bare bones, so I used tutorials and other stack overflow threads to get to solutions. I have something that works, but these scripts send the full lists even if the item quantity cell is empty, which doesn't work for me.

From my understanding I need a If Else statement to do this, but I dont know where to place it.

The goal is to if a cell in the column is empty that row is skipped. How can I do that?

The below is the script that opens the browser and sends the messages.

Sub WebWhatsApp()

Dim pop As Range
Dim BOT As New WebDriver
Dim KS As New Keys
Dim count_row As Integer

count_row = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Dim rng As Range
Set rng = Sheets("Interface").Range(Cells(12, 1), Cells(count_row, 5))
Dim myString As String
myString = Rang2String(rng)

BOT.Start "chrome", "https://web.whatsapp.com/"
BOT.Get "/"

MsgBox _
              "Please scan the QR code." & _
              "After you are logged in, please confirm this message box by clicking 'ok'", vbOKOnly, "WhatsApp Bot"

searchtext = Sheets("Interface").Range(Cells(5, 8), Cells(5, 8))

textmessage = myString

BOT.FindElementByXPath("//*[@id='side']/div[1]/div/label/div/div[2]").Click

BOT.Wait (500)

BOT.SendKeys (searchtext)

BOT.Wait (500)

BOT.SendKeys (KS.Enter)

BOT.Wait (500)

BOT.SendKeys (textmessage)

BOT.Wait (1000)

BOT.SendKeys (KS.Enter)

MsgBox "Done."


End Sub

And this is the script that turns a range of the Excel sheet into a string that the main script sends out as text messages.

Function Rang2String(rng As Range) As String
    
Dim strng As String
Dim myRow As Range
Dim KS As New Keys
    
    With rng
        For Each myRow In .Rows
            strng = strng & Join(Application.Transpose(Application.Transpose(myRow.Value)), " | ") & vbNewLine
        Next
    End With
    
    Rang2String = Left(strng, Len(strng) - 1)
     
End Function

I realize that the answer could be very obvious but I cant seem to see a solution.

Thanks in advance.

Upvotes: 1

Views: 69

Answers (1)

CDP1802
CDP1802

Reputation: 16357

Option Explicit
Function Rang2String(rng As Range) As String
    
    Const COL_QU = "D" ' quantity column
    Dim e As String, myrow As Range

    With Application
        For Each myrow In rng.Rows
            If Len(myrow.Cells(1, COL_QU)) > 0 Then
                Rang2String = Rang2String & e & Join(.Transpose(.Transpose(myrow)), " | ")
                e = vbNewLine
            End If
        Next
    End With
     
End Function

Upvotes: 1

Related Questions