2

I have found below code to select next blank cell in a column. This code finds the next blank cell (data starting from A1). Suppose I have data from A1-A15 then a blank cell. This code does select A16 as it is next blank cell.

However I have more data below and I want to select all blank cells under each set of data. Please advise how to amend this code so that it can go through the whole of column A and select all next blank cells (If I make sense of what I have written). Thanks

Sub FindNextCell()
'Locate the next data entry cell in data entry column A
Dim FirstCell As String
Dim i As Integer
FirstCell = "A1"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
pnuts
  • 58,317
  • 11
  • 87
  • 139
Kaiser
  • 53
  • 2
  • 4
  • 11

1 Answers1

3

If you want to select all the blanks between the first and last cell you could use this

Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & lr).SpecialCells(xlCellTypeBlanks).Select

Note: Avoid using .Select

Community
  • 1
  • 1
  • Hi Me. Thanks for your help in above. I tried something else but it didn't work so need your help again. Can you please advise how to add a sum formula in these selected cells. Selected cell (with the code above) should sum all cells above. So that one macro select all blank cells and sum. Cheers – Kaiser Sep 04 '14 at 10:10
  • @Kaiser I think it would make sense to ask a new question as this would totally invalidate the current question and answer. Hit me up with a link to your new question and I will see what I can do for you. –  Sep 04 '14 at 10:25