Sleeper: Used Column Value

[sheeeng]

Hi all, :hi:

Can I view or get the value of the used column for each row?

Some row may have used 3 column, whereas some used up to 13 column?

Please help. :friends:

Thanks in advance.

[sheeeng]

FYI....It must also can read the number of total columns used & the last column index...

TIA.

[Jacob Hilderbrand]

What do you want to do with the values?

[sheeeng]

I need the value to make a string out of all data-entered cell for each rows.

Different rows may used diferent numbers of columns .

Thx.

[Jacob Hilderbrand]

Like this?


Option Explicit

Sub Macro1()
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim Msg As String
Dim RowMsg As String
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
LastCol = Cells(i, 256).End(xlToLeft).Column
RowMsg = ""
For j = 1 To LastCol
RowMsg = RowMsg & ", " & Cells(i, j).Text
Next j
Msg = Msg & vbNewLine & Mid(RowMsg, 3, Len(RowMsg))
Next i
MsgBox Msg
End Sub

[sheeeng]

That is near to my need.
Thanks, JAKE.

But can it display the last column name for each row & the last column number?

eg. Row1 use up to column "K".

Can it display Row1 : Column K & Number of column used : 11 ? (or other form like it)


Thanks.

[Jacob Hilderbrand]

Try this.


Option Explicit

Sub Macro1()
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim Msg As String
Dim RowMsg As String
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
LastCol = Cells(i, 256).End(xlToLeft).Column
RowMsg = ""
For j = 1 To LastCol
RowMsg = RowMsg & ", " & Cells(i, j).Text
Next j
RowMsg = Mid(RowMsg, 3, Len(RowMsg))
If LastCol > 26 Then
RowMsg = "Row " & i & _
": Column " & Left(Cells(1, LastCol).Address(False, False), 2) & " (" & LastCol & ") " & RowMsg
Else
RowMsg = "Row " & i & _
": Column " & Left(Cells(1, LastCol).Address(False, False), 1) & " (" & LastCol & ") " & RowMsg
End If
Msg = Msg & vbNewLine & RowMsg
Next i
MsgBox Msg
End Sub

[sheeeng]

Try this.


Option Explicit

Sub Macro1()
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim Msg As String
Dim RowMsg As String
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
LastCol = Cells(i, 256).End(xlToLeft).Column
RowMsg = ""
For j = 1 To LastCol
RowMsg = RowMsg & ", " & Cells(i, j).Text
Next j
RowMsg = Mid(RowMsg, 3, Len(RowMsg))
If LastCol > 26 Then
RowMsg = "Row " & i & _
": Column " & Left(Cells(1, LastCol).Address(False, False), 2) & " (" & LastCol & ") " & RowMsg
Else
RowMsg = "Row " & i & _
": Column " & Left(Cells(1, LastCol).Address(False, False), 1) & " (" & LastCol & ") " & RowMsg
End If
Msg = Msg & vbNewLine & RowMsg
Next i
MsgBox Msg
End Sub



Very GOOD. :thumb NICE!

Just a little more to add, can it read last row regradless of which column they are in?

eg. i had only 1 isolated data in row 62.

Cells(62,"J").Value = 890

but the code above ignore this value.

Can it be included?
Because i notice that the code seems only check on column A whether has data then read the row, rite? :help

Thanks in advance.:friends:

[sheeeng]

Could anyone help out....

Hope anyone can come up with code on checking above code to verify the whole row before counting columns..because column A might be empty....the code must check through the whole row before counting column processes starts for the row..

Thx.