going down excel column from specified start place 2

[Magnus53]

Hey everyone

A simple quesion I'm sure. I just need some advice on how to loop through a excel column from a specified cell to the last cell with data in it. The column does have blank cell's but never more then one in a row. Any ideas?

Thanks in advance

 

[Simmy]

Hi Magnus53
This will find the last cell in column A that is not blank
Sub FindLastCell
With ActiveSheet.Range("A65536&quot
If Value <> &quot;&quot; Then
A = .Row
Else
A = .End(xlUp).Row
End If
End With
MsbBox(A)
End Sub

Hope This helps. Regards Simmy

 

[rmikesmith]

Magnus53,

Building on Simmy's example:

Sub LoopThroughRange()
Const FirstUsedRow = 4  'Make this your specified first cell
Const ColNum = 1        'Make this your column (A=1, B=2, etc.)
Dim LastUsedRow As Long
Dim Rng As Range
Dim OneCell As Range

  With ActiveSheet
    If .Cells(65536, ColNum).Value <> &quot;&quot; Then
      LastUsedRow = 65536
    Else
      LastUsedRow.Cells(65536, ColNum).End(xlUp).Row
    End If
    Set Rng = .Range(.Cells(FirstUsedRow, ColNum), .Cells(LastUsedRow, ColNum))
    For Each OneCell In Rng
      'Do stuff with each cell here
    Next OneCell
  End With

End Sub

Notes: I prefer to use the Cells(row,col) representation of a worksheet cell rather than the Range address, which is why my code looks a little different than Simmy's. I've used Constants to specify the starting cell and column number. These could also be represented using variables or hard-coded. I've used the ActiveSheet but you can also explicitly reference a particular worksheet. Lots of different ways to handle these things depending on how flexible you need the code to be.

Regards,
Mike

 

[Magnus53]

Thanks for both reply's!

rmikesmith , I tried your code but it won't compile, gives me an error message &quot; invaild qualifer&quot; on LastUsedRow in LastUsedRow.Cells(65536, ColNum).End(xlUp).Row. Any ideas? Also could you explain what that line is doing? I'm a tad confused by it. Thanks!

 

[Zathras]

Just to offer a third way, take a look at this:

Option Explicit

Sub Demo()
Dim WorkArea As Range
Dim WorkCell As Range
Dim nEmptyCount As Integer
Dim nRowOffset As Integer

Set WorkArea = Selection

' or whatever for first cell

nEmptyCount = 0
nRowOffset = -1
While nEmptyCount < 3
  nRowOffset = nRowOffset + 1
  Set WorkCell = WorkArea.Offset(nRowOffset, 0)
  If WorkCell.Value = &quot;&quot; Then
    nEmptyCount = nEmptyCount + 1
  Else
    nEmptyCount = 0
    With WorkCell

      ' Insert your code here...

      MsgBox .Address
    
    End With
  End If
Wend
Set WorkArea = Nothing
Set WorkCell = Nothing
End Sub

Use whatever technique you want for setting the WorkArea to the single cell that you are starting with. I used the current selection for demo purposes only.

 

[rmikesmith]

Magnus53,

You have a right to be confused! My Typo. The line should be:

LastUsedRow = .Cells(65536,ColNum).End(xlUp).Row

HTH
Mike