Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Tell If Range Contains Any Data?
I'm doing validation checking on a spreadsheet whose data will be
imported into a data base if the spreadsheet's formatting and layout look right. On a given worksheet, once I think I've found the last row of data, I'd like to set a .Range=(a whole bunch of cells after what I think is the last data row) and then check to see if there are any values in the composite of all those cells. If there are any values, that would suggest that the worksheet's format is wanting and I'd inform the user accordingly. Bottom Line: If I've got a Range, is there a syntactically-simple way to see if there's any data in it? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Tell If Range Contains Any Data?
Hi Pete.
Try: Set Rng = Range("A1:A100") If Application.CountA(Rng) = 0 Then MsgBox "Its empty!" End If --- Regards, Norman "PeteCresswell" wrote in message ups.com... I'm doing validation checking on a spreadsheet whose data will be imported into a data base if the spreadsheet's formatting and layout look right. On a given worksheet, once I think I've found the last row of data, I'd like to set a .Range=(a whole bunch of cells after what I think is the last data row) and then check to see if there are any values in the composite of all those cells. If there are any values, that would suggest that the worksheet's format is wanting and I'd inform the user accordingly. Bottom Line: If I've got a Range, is there a syntactically-simple way to see if there's any data in it? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Tell If Range Contains Any Data?
If Application.CountA(Rng) = 0 Then
That nailed it.... *exactly* what I was trolling for. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Tell If Range Contains Any Data?
Set Rng = Range("A1:A100") No good deed goes unpunished. At the risk of wearing out my welcome... Can I write some kind of loop to iterate through that range and pick off the Row/Column numbers and cell values of the offending (i.e. those that have values) cells? Then I could include at least some of the offending cell locations/ values in my error notification.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concise Way To Tell If Range Contains Any Data?
Hi Pete,
Instead of looping, try usung the specialcells method, For example, if the values did not include formulae, you could try something like: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Set WB = Workbooks("MyBook.xls") '<<=== CHANGE Set SH = WB.Sheets("Sheet1") '<<=== CHANGE Set Rng = SH.Range("A1:A100") '<<=== CHANGE On Error Resume Next Set Rng = Rng.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If Not Rng Is Nothing Then 'do something, e.g.: Debug.Print Rng.Address(0, 0) End If End Sub '<<============= --- Regards, Norman "PeteCresswell" wrote in message ups.com... Set Rng = Range("A1:A100") No good deed goes unpunished. At the risk of wearing out my welcome... Can I write some kind of loop to iterate through that range and pick off the Row/Column numbers and cell values of the offending (i.e. those that have values) cells? Then I could include at least some of the offending cell locations/ values in my error notification.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
More Concise Way To Assign Borders? | Excel Programming | |||
Make instructions clear, concise and in plain english. | Excel Discussion (Misc queries) | |||
Concise border removal method | Excel Programming |