I'm a VBA rookie looking to write some code that will return the value of the first non-blank cell in a row spanning 5 columns of data. I found a mrexcel article that contains exactly what I'm looking to achieve but I'm having a hard time translating the Excel formula to VBA.
/
I had mild success with a basic If-Then statement but that only works if the column directly to the right is not empty; if the column directly to the right is blank the code carries over the blank. I'd like the code to run on the same row, moving right until a non-empty column is reached.
Sub test()
lRow = Cells(Rows.Count, "D").End(xlUp).Row
For i = 2 To lRow
If IsEmpty(Cells(i, 7).Value) Then
Cells(i, 7).Value = Cells(i, 8).Value
ElseIf IsEmpty(Cells(i, 8).Value) Then
Cells(i, 7).Value = Cells(i, 9).Value
End If
Next
End Sub
I'm a VBA rookie looking to write some code that will return the value of the first non-blank cell in a row spanning 5 columns of data. I found a mrexcel article that contains exactly what I'm looking to achieve but I'm having a hard time translating the Excel formula to VBA.
https://www.mrexcel.com/excel-tips/find-the-first-non-blank-value-in-a-row/
I had mild success with a basic If-Then statement but that only works if the column directly to the right is not empty; if the column directly to the right is blank the code carries over the blank. I'd like the code to run on the same row, moving right until a non-empty column is reached.
Sub test()
lRow = Cells(Rows.Count, "D").End(xlUp).Row
For i = 2 To lRow
If IsEmpty(Cells(i, 7).Value) Then
Cells(i, 7).Value = Cells(i, 8).Value
ElseIf IsEmpty(Cells(i, 8).Value) Then
Cells(i, 7).Value = Cells(i, 9).Value
End If
Next
End Sub
Copy
Sub CopyLeftMost()
' Define constants.
Const FIRST_ROW As Long = 2
Const LAST_ROW_COLUMN As Long = 4 ' make sure this column is populated!
Const FIRST_COLUMN As Long = 7
Const COLUMNS_COUNT As Long = 5
Const RESULT_COLUMN As Long = 7
Const NOT_AVAILABLE_VALUE As Variant = "#N/A"
' Reference the worksheet.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Retrieve the last populated row.
Dim LastRow As Long:
LastRow = ws.Cells(ws.Rows.Count, LAST_ROW_COLUMN).End(xlUp).Row
' Declare addtional variables.
Dim Value As Variant, r As Long, c As Long, IsFound As Boolean
' Loop through the rows...
For r = FIRST_ROW To LastRow
IsFound = False ' reset the flag for each row
' Loop through the columns...
For c = 1 To COLUMNS_COUNT
' Retrieve the value from each column of the current row.
Value = ws.Cells(r, FIRST_COLUMN + c - 1).Value
' Either...
If Not IsEmpty(Value) Then ' is not empty
' ... or
'If Len(CStr(Value)) > 0 Then ' is not blank
IsFound = True ' since the condition is met...
Exit For ' ... there is no need to check anymore
End If
Next c
If IsFound Then ' condition met
ws.Cells(r, RESULT_COLUMN).Value = Value
Else ' condition not met
ws.Cells(r, RESULT_COLUMN).Value = NOT_AVAILABLE_VALUE
End If
Next r
MsgBox "Copied left most values.", vbInformation
End Sub
Move
Sub MoveValuesToTheLeft()
' Define constants.
Const NOT_AVAILABLE_VALUE As Variant = "#N/A"
Const FIRST_ROW_ADDRESS As String = "G2:K2"
' Reference the worksheet.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Declare additional variables.
Dim rg As Range, lcell As Range, RowsCount As Long, ColumnsCount As Long
' Reference the range and retrieve its number of rows and columns.
With ws.Range(FIRST_ROW_ADDRESS)
ColumnsCount = .Columns.Count
Set rg = .Resize(ws.Rows.Count - .Row + 1) ' i.e. G2:K1048576
Set lcell = rg.Find("*", , xlFormulas, , xlByRows, xlPrevious)
If lcell Is Nothing Then Exit Sub ' no data (highly unlikely)
RowsCount = lcell.Row - .Row + 1
Set rg = .Resize(RowsCount)
End With
' Return the values of the range in an array.
Dim Data() As Variant: Data = rg.Value
' Declare addtional variables.
Dim Value As Variant, r As Long, c As Long, IsFound As Boolean
' Loop through the rows...
For r = 1 To RowsCount
IsFound = False ' reset the flag for each row
' Loop through the columns...
For c = 1 To ColumnsCount
' Retrieve the value from each column of the current row.
Value = Data(r, c)
' Either...
If Not IsEmpty(Value) Then ' is not empty
' ... or
'If Len(CStr(Value)) > 0 Then ' is not blank
IsFound = True ' since the condition is met...
Exit For ' ... there is no need to check anymore
End If
Next c
' Move value to the left.
If IsFound Then ' condition met
Data(r, 1) = Value
Else ' condition not met
Data(r, 1) = NOT_AVAILABLE_VALUE
End If
Next r
' Write the resulting values from the first array column to the worksheet.
rg.Columns(1).Value = Data
' Clear all but the first column.
rg.Resize(, ColumnsCount - 1).Offset(, 1).ClearContents
' Inform.
MsgBox "Moved values to the left.", vbInformation
End Sub
Your code needs a slight mod with the usage of the End property of the Range object.
Sub test()
lRow = Cells(Rows.count, "D").End(xlUp).Row
For i = 2 To lRow
If IsEmpty(Cells(i, 7).Value) Then
' Cells(i, 7).Value = Cells(i, 8).Value
'ElseIf IsEmpty(Cells(i, 8).Value) Then
' Cells(i, 7).Value = Cells(i, 9).Value
Cells(i, 7) = Cells(i, 7).End(xlToRight)
Range("H" & i & ":K" & i).Clear
End If
Next
End Sub
C2:K12to the corresponding row of columnA. 1.) Where do you want to return the results? 2.) What are your columns? For example, 1.)G2:GLastRowand 2.)G2:KLastRow. Or what is the formula for your specific requirement? – VBasic2008 Commented Jan 2 at 18:44