Loop to Find Last Cell Containing Data 3

[cghoga]

Hello, I have a question related to looping through rows and columns and grabbing the last cell location containing data.

This is hard to explain so please bear with me.

I have started with something like below to search in column 1.

For Counter = 6 To 500

Set curCell = Worksheets("Project List").Cells(Counter, 1)

If curCell.Value = "" Then

varcell = Counter

Counter = 500

End If

Next Counter

The above grabs the first cell in column 1 that does not contain data. However, if a row is left blank, and the next row contains data it stops short.

For example, from the following, the value I would be looking for would be C5.

|A|B|C|
1|x|x|x|
2|x| | |
3| | | |
4| | | |
5| | |x|

Your help is greatly appreciated.

Thanks!

 

[SkipVought]

Hi,

dim lRow as long, lRowFirst as long, lRowLast as long
with Worksheets("Project List").[A1].CurrentRegion
  lrowfirst = .row
  lrowlast = lrowfirst + .rows.count -1
  for lrow = lrowfirst to lrowlast
    'do yer stuff
     msgbox .cells(lrow, "A").Value
  next
End with

Skip,

_[red][/red]

 

[WinblowsME]

Based on

http://www.tek-tips.com/faqs.cfm?fid=2112

Sub Find_Last_Cell()
   Dim j As Long, last_row As Long, last_col As Integer
   
   last_row = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
   last_col = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
        
   For j = last_col To 1 Step -1
      curr_cell = Trim(Cells(last_row, j))
      
      If curr_cell <> "" Then
         Exit For
      End If
   Next j
   
   MsgBox Cells(last_row, j).Address
End Sub

 

[SkipVought]

Why do you have TOTALLY EMPTY ROWS in your table???

That is NOT a valid definition of a table in Excel, ie Contiguous data.

Guidelines for entering data on a worksheet
Microsoft Excel has a number of features that make it easy to manage and analyze data. To take advantage of these features, enter data in your worksheet according to the following guidelines.

Data organization

Put similar items in one column Design the data so that all rows have similar items in the same column.

Keep the range separate Leave at least one blank column and one blank row between the related data range and other data on the worksheet. Excel can then more easily detect and select the range when you sort, filter, or insert automatic subtotals.

Position critical data above or below the range Avoid placing critical data to the left or right of the range; the data might be hidden when you filter the range.

Show rows and columns Make sure any hidden rows or columns are displayed before making changes to the range. When rows and columns in a range are not showing, data can be deleted inadvertently.

Data format

Use formatted column labels Create column labels in the first row of the range of data. Excel uses the labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the range. Format the cells as text before you type the column labels.

Use cell borders When you want to separate labels from data, use cell borders— not blank rows or dashed lines— to insert lines below the labels.

Avoid blank rows and columns Avoid putting blank rows and columns in the range so that Excel can more easily detect and select the related data range.

Don't type leading or trailing spaces Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell.

Extend data formats and formulas When you add new rows of data to the end of a data range, Excel extends consistent formatting and formulas. Three of the five preceding cells must use the same format for a format to be extended. All of the preceding formulas must be consistent for a formula to be extended.

List feature

You can designate a contiguous range of cells on your worksheet as a list. When you create a list, data defined by the list can be manipulated independently of data outside of the list. After you create a list, you can use list features to quickly sort, filter, total, or publish the data contained within the list.

You can also use the list feature to compartmentalize sets of related data by organizing that data using multiple lists on a single worksheet.

Skip,

_[red][/red]

 

[cghoga]

Thanks to everyone, great posts!

 

[AnotherHiggins]

I (nearly) always avoid looping whenever possible.

Here's another option:

Sub FindLastCell()
TtlCol = Application.Columns.Count
TtlRow = Application.Rows.Count

MsgBox _
    "The last populated cell is " & _
    Cells.Find(What:="*", After:=Cells(TtlRow, TtlCol), SearchDirection:=xlPrevious).Address
End Sub

If you don't want the dollar signs in the answer, I'd do this:

Sub FindLastCell()
TtlCol = Application.Columns.Count
TtlRow = Application.Rows.Count
LastPopCell = _
    Cells.Find(What:="*", After:=Cells(TtlRow, TtlCol), SearchDirection:=xlPrevious).Address

MsgBox _
    "The last populated cell is " & _
    Replace(LastPopCell, "$", "")
End Sub

In case you're wondering, I've used variables to find the number of columns and rows because Excel 2007 jumps from 256 columns and 65536 rows to 65536 columns and 1048576 rows. So this will search from the very last cell available in any version.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Loomah]

or you could use
.address(false, false)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​