Look at first empty row after the first few rows of my excel are empty but the rest are populated

0 votes

Now, my Excel macro is concatenating the unit and number columns. The numbers are retrieved based on an ID, and occasionally the number I want to combine the unit with may not be available for that particular ID. The number I want to concatenate with the unit only starts on row 6, occasionally on row 8, but it will at least start on row 2. I am using find last row, but that does not always work. The title appears on row 1.

Without giving it a range to look up to, say, row 100 because I occasionally have more than 100 rows, I want it to disregard the empty rows. The code below is what I currently have and works if the column is fully populated until the end.

rowEmpty = 2
    Do While IsEmpty(ws_Export.cells(rowEmpty, 9)) = False
        rowEmpty = rowEmpty + 1
    'rowEmpty is now set as the first empty row (sets the range of the table)
    'Add units within the same cell as the shunt
    For s = 2 To rowEmpty - 1
        cells(s, 9) = cells(s, 9) & " " & cells(s, 8)
Next s

two columns I wish to concatanate

Mar 30, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

Concatenate If Not Blank

  • Replace the J with I to replace as required.

enter image description here

Option Explicit

Sub AppendUnits()

    Const WS_NAME As String = "Export"
    Const CAL_FIRST_CELL As String = "I2"
    Const UNIT_COLUMN As String = "H"
    Const DST_COLUMN As String = "J" ' Result
    Const DELIMITER As String = " "
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets(WS_NAME)
    Dim crg As Range, rCount As Long
    With ws.Range(CAL_FIRST_CELL)
        Dim lCell As Range: Set lCell = .Resize(.Worksheet.Rows.Count _
            - .Row + 1).Find("*", , xlFormulas, , , xlPrevious)
        If Not lCell Is Nothing Then
            rCount = lCell.Row - .Row + 1
            Set crg = .Resize(rCount)
        End If
    End With
    If crg Is Nothing Then
        MsgBox "No data found.", vbCritical
        Exit Sub
    End If
    Dim urg As Range: Set urg = crg.EntireRow.Columns(UNIT_COLUMN)
    Dim cData(), uData()
    If rCount = 1 Then
        ReDim cData(1 To 1, 1 To 1): cData(1, 1) = crg.Value
        ReDim uData(1 To 1, 1 To 1): uData(1, 1) = urg.Value
        cData = crg.Value
        uData = urg.Value
    End If
    Dim r As Long, rStr As String
    For r = 1 To rCount
        rStr = CStr(cData(r, 1))
        If Len(rStr) > 0 Then
            cData(r, 1) = rStr & DELIMITER & CStr(uData(r, 1))
        End If
    Next r
    Dim drg As Range: Set drg = crg.EntireRow.Columns(DST_COLUMN)
    drg.Value = cData
    MsgBox "Units appended.", vbInformation
End Sub
answered Mar 30, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How do you calculate the Quintile for groups of rows in Excel?

Use this formula: =MAX(1,ROUNDUP(10*PERCENTRANK($C:$C,$C2,4),0)) To divide into whichever many ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer
0 votes
1 answer

How do I combine the first character of a cell with another cell in Excel?


answered Nov 7, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,700 points
0 votes
1 answer
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP