Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to select cells with values only (not empty ones)? | Excel Discussion (Misc queries) | |||
How to select cells with values only (not empty ones)? | Excel Discussion (Misc queries) | |||
Summing a variably-sized range | Excel Worksheet Functions | |||
How to use linest with variably sized data arrays? | Excel Worksheet Functions | |||
Macro to select non empty rows in a given range | Excel Programming |