Select variably-sized range which contains empty cells
 

Hi all,

I have some data that I imported from Access into an Excel spreadsheet.
Included in all the columns, are two which contain date values. Some
cells are empty, i.e. there is no date.

What I want to do is replace all the empty cells with the text
,,Open". BUT the tricky part is that I need to do this through VB
code. The hard part is that I dont know beforehand how many records are
imported from Access. If I did know, I could just use some code that
selects the range up to where the first empty cell is met, i.e. like

..Range(.Range("g4"), .Range("h4").End(xlDown))

BUT in my case I have empty cells inside the range already! Therefore I
cannot use that. I was thinking of maybe getting the recordset's
RecordCount property from Access and using that variable to iterate
through the cells in Excel, but thought there may be a less complicated
way of doing this.

Can somebody please suggest a way out of my troubles?

Thanks!

J

Select variably-sized range which contains empty cells
 

Hi Jean,

Try:

'=========================
Sub Tester()
Dim rng As Range, rng2 As Range
Dim i As Long
i = Cells(Rows.Count, "G").End(xlUp).Row

Set rng = Range("G1").Resize(i, 2)

On Error Resume Next
Set rng2 = rng.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng2 Is Nothing Then rng2.Value = ",,Open"

End Sub

'<<=========================


---
Regards,
Norman



"Jean" wrote in message
oups.com...
Hi all,

I have some data that I imported from Access into an Excel spreadsheet.
Included in all the columns, are two which contain date values. Some
cells are empty, i.e. there is no date.

What I want to do is replace all the empty cells with the text
,,Open". BUT the tricky part is that I need to do this through VB
code. The hard part is that I dont know beforehand how many records are
imported from Access. If I did know, I could just use some code that
selects the range up to where the first empty cell is met, i.e. like

.Range(.Range("g4"), .Range("h4").End(xlDown))

BUT in my case I have empty cells inside the range already! Therefore I
cannot use that. I was thinking of maybe getting the recordset's
RecordCount property from Access and using that variable to iterate
through the cells in Excel, but thought there may be a less complicated
way of doing this.

Can somebody please suggest a way out of my troubles?

Thanks!

J

Select variably-sized range which contains empty cells
 

Jean,

..Range("G:H").SpecialCells(xlCellTypeBlanks).Valu e = "Open"

HTH,
Bernie
MS Excel MVP


"Jean" wrote in message
oups.com...
Hi all,

I have some data that I imported from Access into an Excel spreadsheet.
Included in all the columns, are two which contain date values. Some
cells are empty, i.e. there is no date.

What I want to do is replace all the empty cells with the text
,,Open". BUT the tricky part is that I need to do this through VB
code. The hard part is that I dont know beforehand how many records are
imported from Access. If I did know, I could just use some code that
selects the range up to where the first empty cell is met, i.e. like

.Range(.Range("g4"), .Range("h4").End(xlDown))

BUT in my case I have empty cells inside the range already! Therefore I
cannot use that. I was thinking of maybe getting the recordset's
RecordCount property from Access and using that variable to iterate
through the cells in Excel, but thought there may be a less complicated
way of doing this.

Can somebody please suggest a way out of my troubles?

Thanks!

J

Select variably-sized range which contains empty cells
 

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value = "" Then
Cells(i,"A").Value = "Open"
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean" wrote in message
oups.com...
Hi all,

I have some data that I imported from Access into an Excel spreadsheet.
Included in all the columns, are two which contain date values. Some
cells are empty, i.e. there is no date.

What I want to do is replace all the empty cells with the text
,,Open". BUT the tricky part is that I need to do this through VB
code. The hard part is that I dont know beforehand how many records are
imported from Access. If I did know, I could just use some code that
selects the range up to where the first empty cell is met, i.e. like

.Range(.Range("g4"), .Range("h4").End(xlDown))

BUT in my case I have empty cells inside the range already! Therefore I
cannot use that. I was thinking of maybe getting the recordset's
RecordCount property from Access and using that variable to iterate
through the cells in Excel, but thought there may be a less complicated
way of doing this.

Can somebody please suggest a way out of my troubles?

Thanks!

J

Select variably-sized range which contains empty cells
 

Thanks for both your quick replies. I went with Bernie's solution and
it works great.

All I need to do is now delete the bottom 3 cells in my range, they
should not have "Open" in them.
I was thinking that one should use the following, but how do I select a
range with Offset?

.Range("G:H").SpecialCells(xlCellTypeBlanks).Value = "offen" '
works great
.Range("g4").End(xlDown).Select
.Selection.Offset(-1, 0).Select
'How do I select a range from an offset witout using actual
cell/range references
.Selection.ClearContents

Thanks again!

Select variably-sized range which contains empty cells
 

I only know if in Excel. You can get the last row by finding from down
to top. Assuming your column is E

Sub OpenForBlanks()

Dim lngLastRow As Long
Dim rngWork As Range
lngLastRow = Cells(Rows.Count, "E").End(xlUp).Row

Set rngWork = Range("E1:E" & lngLastRow)
rngWork.Cells.SpecialCells(xlCellTypeBlanks).Value = "OPEN"

End Sub

Is it like what you wanted to do? Sorry if I misunderstood you.

Best regards,


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=397721

Select variably-sized range which contains empty cells
 

Jean,

Try:

..Range("G:H").SpecialCells(xlCellTypeBlanks).Valu e = "offen" '
..Range("G4").End(xlDown)(-1, 1).Resize(3, 1).ClearContents

HTH,
Bernie
MS Excel MVP


"Jean" wrote in message
oups.com...
Thanks for both your quick replies. I went with Bernie's solution and
it works great.

All I need to do is now delete the bottom 3 cells in my range, they
should not have "Open" in them.
I was thinking that one should use the following, but how do I select a
range with Offset?

.Range("G:H").SpecialCells(xlCellTypeBlanks).Value = "offen" '
works great
.Range("g4").End(xlDown).Select
.Selection.Offset(-1, 0).Select
'How do I select a range from an offset witout using actual
cell/range references
.Selection.ClearContents

Thanks again!

Select variably-sized range which contains empty cells
 

Try the usedrange property of the activesheet object to either make a
selection or count the rows and use this as the basis for a loop.

Alternatively, could create a nested loop that checks across the
columns of your spreadsheet to count the blank cells

Dim Counter as Integer
Dim BlankCounter
Counter = 1
Do
BlankCounter = 0
For J = 1 to 10
If Cells(Counter, J)="" Then BlankCounter = BlankCounter + 1
Next J
If BlankCounter = 10 Then Exit Do
Loop

where the upper limit of J is the number of columns imported. Counter
becomes the first blank row in your worksheet.

Easy!


--
ronthedog
------------------------------------------------------------------------
ronthedog's Profile: http://www.excelforum.com/member.php...o&userid=26504
View this thread: http://www.excelforum.com/showthread...hreadid=397721

Select variably-sized range which contains empty cells
 

Hi Bob,

I tried that, but the problem is when my last cell of my range is
"coincidentally" a blank value, then it doesnt get marked as "Open"

Using my knowledge of iterating and knowing that maybe i should iterate
one cell extra, i tried this then, but it gives a "Rows" error:

For i = 1 To (Cells(Rows.Count,"A").End(xlUp).Row + 1)

?




Bob Phillips wrote:
For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value = "" Then
Cells(i,"A").Value = "Open"
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean" wrote in message
oups.com...
Hi all,

I have some data that I imported from Access into an Excel spreadsheet.
Included in all the columns, are two which contain date values. Some
cells are empty, i.e. there is no date.

What I want to do is replace all the empty cells with the text
,,Open". BUT the tricky part is that I need to do this through VB
code. The hard part is that I dont know beforehand how many records are
imported from Access. If I did know, I could just use some code that
selects the range up to where the first empty cell is met, i.e. like

.Range(.Range("g4"), .Range("h4").End(xlDown))

BUT in my case I have empty cells inside the range already! Therefore I
cannot use that. I was thinking of maybe getting the recordset's
RecordCount property from Access and using that variable to iterate
through the cells in Excel, but thought there may be a less complicated
way of doing this.

Can somebody please suggest a way out of my troubles?

Thanks!

J

Select variably-sized range which contains empty cells
 

Wow, Bernie you are the man. Thanks

Bernie Deitrick wrote:
Jean,

Try:

.Range("G:H").SpecialCells(xlCellTypeBlanks).Value = "offen" '
.Range("G4").End(xlDown)(-1, 1).Resize(3, 1).ClearContents

HTH,
Bernie
MS Excel MVP


"Jean" wrote in message
oups.com...
Thanks for both your quick replies. I went with Bernie's solution and
it works great.

All I need to do is now delete the bottom 3 cells in my range, they
should not have "Open" in them.
I was thinking that one should use the following, but how do I select a
range with Offset?

.Range("G:H").SpecialCells(xlCellTypeBlanks).Value = "offen" '
works great
.Range("g4").End(xlDown).Select
.Selection.Offset(-1, 0).Select
'How do I select a range from an offset witout using actual
cell/range references
.Selection.ClearContents

Thanks again!

Select variably-sized range which contains empty cells
 

Jean,

Rows is a property of a range object, not an object, so you need to use:

For i = 1 To (Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1)

HTH,
Bernie
MS Excel MVP


"Jean" wrote in message
oups.com...
Hi Bob,

I tried that, but the problem is when my last cell of my range is
"coincidentally" a blank value, then it doesnt get marked as "Open"

Using my knowledge of iterating and knowing that maybe i should iterate
one cell extra, i tried this then, but it gives a "Rows" error:

For i = 1 To (Cells(Rows.Count,"A").End(xlUp).Row + 1)

?




Bob Phillips wrote:
For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value = "" Then
Cells(i,"A").Value = "Open"
End If
Next i

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jean" wrote in message
oups.com...
Hi all,

I have some data that I imported from Access into an Excel spreadsheet.
Included in all the columns, are two which contain date values. Some
cells are empty, i.e. there is no date.

What I want to do is replace all the empty cells with the text
,,Open". BUT the tricky part is that I need to do this through VB
code. The hard part is that I dont know beforehand how many records are
imported from Access. If I did know, I could just use some code that
selects the range up to where the first empty cell is met, i.e. like

.Range(.Range("g4"), .Range("h4").End(xlDown))

BUT in my case I have empty cells inside the range already! Therefore I
cannot use that. I was thinking of maybe getting the recordset's
RecordCount property from Access and using that variable to iterate
through the cells in Excel, but thought there may be a less complicated
way of doing this.

Can somebody please suggest a way out of my troubles?

Thanks!

J

Select variably-sized range which contains empty cells
 

Thanks, I have so many ideas now.

I will defnitely come back to this thread sometime for the good ideas.

Regards,

Jean