I am doing a VBA project. It's been a long time since I've done one and I'm having some trouble getting started. I need to loop through the first column in spreadsheet. I don't remember how to refer to the spreadsheet and the columns and loop through it. Can anyone offer some advice?
03-27-2003, 10:11 AM
Something like this ?
Private Sub CommandButton2_Click()
Dim c As Range
For Each c In Range("A:A")
If c.Value = "al23" Then
MsgBox "al23 found in " & c.Address
Thanks that worked. How do I loop from A:4 until the last populated cell (not all cells from A:4 till the last are populated but every 4 or so are)?
03-27-2003, 10:52 AM
The code above will check every cell in Column "A" empty or not.
If you want to check only the non empty cells in the range you can change the code in :
For Each c In Range("A:A").SpecialCells(xlCellTypeConstants)
Don't know for sure how to define a range from "A4" to the last polulated cell if there are blanks cell in between them.
I have an excel spreadsheet. The first column is a bunch of names. The next columns are information about those names. About every fourth row is a new name. (All other rows fields in the name column are empty) I need to print out the information about each name seperately. So I have looped through the whole first column and identified each name. Now once I get the name I need to loop until the next populated field. This is where I'm a little stuck right now. I then need to copy all the fields in that range onto a new sheet ( I haven't even gotten to that step yet!)
03-27-2003, 05:43 PM
Following example shows how to achieve the target:
Dim wbBook As Workbook
Dim wsSheet1 As Worksheet, wsSheet2 As Worksheet
Dim rnData As Range, rnCell As Range, rnTarget As Range
Set wbBook = ThisWorkbook
Set wsSheet1 = .Worksheets("Sheet1")
Set wsSheet2 = .Worksheets("Sheet2")
Set rnData = .Range(.Range("A4"), .Range("A65536").End(xlUp))
For Each rnCell In rnData
If Not IsEmpty(rnCell.Value) Then
Set rnTarget = .Range("A65536").End(xlUp)
rnTarget.Offset(1).Resize(1, 4).Value = rnCell.Resize(1, 4).Value