This should do what you want:
Public Sub GetNonBlank()
Dim TotalRows As Integer
Dim LastCell As Integer
TotalRows = Cells.Find("*", ActiveCell.SpecialCells(xlLastCell), , ,
xlByRows, xlPrevious).row
For i = 1 To TotalRows
With ActiveSheet
LastCell = .Cells(i, .Columns.Count).End(xlToLeft).Column
End With
For j = 1 To LastCell
If Cells(i, j).Value <> "" Then
MsgBox Cells(i, j).Value
End If
Next
Next
End Sub
On Fri, Mar 27, 2009 at 6:34 AM, xxx <lavanyaredd...@gmail.com> wrote:
>
> I have a excel table as below
>
> Xxx 1 5
> Yyy Fgf Dfd
> Zzz dfg Dsgf
> qqq 4 fg dfg
> ....
>
> i need to get all the values of occupied cells in every row...
> so in the 1st row...i need to get the values
> xxx
> 1 and 5 excluding space in 3rd column
> similarly in 3rd row : zzz, dfg, dsgf leaving empty space in 2nd
> column
>
> I have written code as below, which loops over number of occupied
> cells and is taking the empty space in 3rd column of 1st row skipping
> 4rt column since my numColInEachRow = 3 in 1st row
> so the value 5 in 4rt column is missing....
>
> totalRows = ActiveSheet.UsedRange.Rows.Count
>
> For i = 1 To totalRows
> numColInEachRow = Application.WorksheetFunction.CountA(Range(Cells
> (i, 1), Cells(i, 100)))
> For j = 1 To numColInEachRow
> ColValue = ActiveSheet.Cells(i, j).Value
> Next j
> Next i
>
>
> thank you all for any help
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
