I have a range of cells in one row, and wanted to test if they all do not have content. From the database i found following solution to fix my problem.
If Not IsError(Application.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
I fixed the ranges to be more like my needs
If Not IsError(Application.Match("", rRng.Offset(lOffset).Range(Cells(1, 1), Cells(1, rRng.Columns.Count)), 0)) Then
It didn't jump into my conditional statement, I tried the opposite
If Not IsError(Application.Match("<>", rRng.Offset(lOffset).Range(Cells(1, 1), Cells(1, rRng.Columns.Count)), 0)) Then
Still it didn't hit my conditional statement. Something other has to cause the problem, but I don't know where to look or what to look for. Just found a cookbook explanation :-). Started by checking that a range really existed
rRng.Offset(lCorrection).Range(Cells(1, 1), Cells(1, rRng.Columns.Count)).Select
and correct range is selected. I believe the condition "" or "<>" are correct input, its simpler than put in a cell reference... What about the looping, is there something I miss. Is there anyone that know what's the problem? In advance thank's
I have a range of cells in one row, and wanted to test if they all do not have content. From the database i found following solution to fix my problem.
If Not IsError(Application.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
I fixed the ranges to be more like my needs
If Not IsError(Application.Match("", rRng.Offset(lOffset).Range(Cells(1, 1), Cells(1, rRng.Columns.Count)), 0)) Then
It didn't jump into my conditional statement, I tried the opposite
If Not IsError(Application.Match("<>", rRng.Offset(lOffset).Range(Cells(1, 1), Cells(1, rRng.Columns.Count)), 0)) Then
Still it didn't hit my conditional statement. Something other has to cause the problem, but I don't know where to look or what to look for. Just found a cookbook explanation :-). Started by checking that a range really existed
rRng.Offset(lCorrection).Range(Cells(1, 1), Cells(1, rRng.Columns.Count)).Select
and correct range is selected. I believe the condition "" or "<>" are correct input, its simpler than put in a cell reference... What about the looping, is there something I miss. Is there anyone that know what's the problem? In advance thank's
Share Improve this question asked Mar 17 at 10:08 Jo-Helge RorvikJo-Helge Rorvik 393 bronze badges 11 | Show 6 more comments4 Answers
Reset to default 1You can also use CountIf in a way similar to that you tried to use Match :
MsgBox Application.CountIf(rRng.Offset(lOffset), "<>") ' counts non-empty cells
MsgBox Application.CountIf(rRng.Offset(lOffset), "") ' counts empty cells
main()
set og_NameRng = rRng
If CorrectRangeForHeaderRows(rRng) Then
If CorrectForTotalRow(rRng) Then
Set og_Rng = rRng
bFound = True
End If
End If
Private Function CorrectRangeForHeaderRows(ByRef rRng As Range) As Boolean
rRng.Select
Dim lCorrection As Long: lCorrection = 0
Dim vVar As Variant
ActiveSheet.rRng.Offset(-(lCorrection + 1)).Range(Cells(1, 1), Cells(1, ActiveSheet.rRng.Columns.Count)).Select
vVar = Application.Match("", ActiveSheet.rRng.Offset(-(lCorrection + 1)).Range(Cells(1, 1), Cells(1, ActiveSheet.rRng.Columns.Count)), 0)
If Not IsError(Application.Match("", ActiveSheet.rRng.Offset(-(lCorrection + 1)).Range(Cells(1, 1), Cells(1, ActiveSheet.rRng.Columns.Count)), 0)) Then _
Debug.Print vVar
CorrectRangeForHeaderRows = True
Set rRng = rRng.Resize(lCorrection)
End Function
Private Function CorrectForTotalRow(ByRef rRng As Range) As Boolean
'The problem is that the position we want to search for do not have content in first Column but later
rRng.Select
Dim lCorrection As Long: lCorrection = rRng.Rows.Count
Dim vVar As Variant
ActiveSheet.rRng.Offset(lCorrection - 1).Range(Cells(1, 1), Cells(1, ActiveSheet.rRng.Columns.Count)).Select
vVar = Application.Match("", ActiveSheet.rRng.Offset(lCorrection - 1).Range(Cells(1, 1), Cells(1, ActiveSheet.rRng.Columns.Count)), 0)
If Not IsError(Application.Match("", ActiveSheet.rRng.Offset(lCorrection - 1).Range(Cells(1, 1), Cells(1, rRng.Columns.Count)), 0)) Then _
Debug.Print vVar
Set rRng = rRng.Resize(lCorrection)
rRng.Select
CorrectForTotalRow = True
I just want to let you know, that this is only some test code to find out how it works, more testing and error-checking has to come
I'will just tell you that I have to leave now and will be back tomorow, but I hope you have a kind of solution to the problem. I tested my self and got to the errormessage 2042 and might be I have to add sheetname
Thanks.
Scan up or down to find first row without values using WorksheetFunction.CountA(). Assume rng is a single row.
Function GetHeaderRow(ByRef rng As Range) As Boolean
Dim i As Long, r As Long, tbl As ListObject
' check if table
Set tbl = rng.ListObject
If tbl Is Nothing Then
r = rng.Row
'scan up for blank row
Do While WorksheetFunction.CountA(rng.Offset(-i)) > 0
i = i + 1
If i = r Then Exit Do
Loop
If i = r Or i = 0 Then
'input rng is empty
Set rng = Nothing
Exit Function
Else
' row below empty row
Set rng = rng.Offset(1 - i)
End If
Else
Set rng = tbl.HeaderRowRange
End If
GetHeaderRow = True
End Function
Function GetTotalsRow(ByRef rng As Range) As Boolean
Dim i As Long, r As Long, tbl As ListObject
' check if table
Set tbl = rng.ListObject
If tbl Is Nothing Then
r = rng.Row
'scan down for blank row
Do While WorksheetFunction.CountA(rng.Offset(i)) > 0
i = i + 1
' limit i
If r + i > Rows.Count Then
i = 0
Exit Do
End If
Loop
If i = 0 Then
' input rng is empty
Set rng = Nothing
Exit Function
Else
' row above empty row
Set rng = rng.Offset(i - 1)
End If
Else
Set rng = tbl.ListRows(tbl.ListRows.Count).Range
End If
GetTotalsRow = True
End Function
Thanks to CDP1802 contribution. I've followed and used his code as a template. I have some constrains. It's part of an small accountant system where both income and expenses are displayed on same sheet. Therefore I couldn't test against 1 or first Row, but I said if the header/total ain't shown up within 3 rows, the I will break. My test criteria if it's a header row, all columns has to have content. My test criteria for total row is that it different from a totally empty row. Some of the rows are not completely empty outside of the "Range" of the table, therefore I a little bit stubborn :-)) and use only first column to last column of the table and offset that with a certain amount of rows like this
Private Function CorrectRangeForHeaderRows(rRng As Range) As Range
'rRng.Select
Dim tmpRng As Range
Set tmpRng = rRng.Range(Cells(1, 1), Cells(1, rRng.Columns.Count))
'tmpRng.Select
'Loop to a full Header row
Dim lCor As Long: lCor = 1
Do While WorksheetFunction.CountA(tmpRng.Offset(-lCor)) < rRng.Columns.Count
lCor = lCor + 1
If rRng.Row - lCor <= 1 Then
Exit Do
End If
Loop
If rRng.Row - lCor > 1 Then
Set rRng = rRng.Offset(-lCor).Resize(rRng.Rows.Count + lCor)
End If
'rRng.Select
CorrectRangeForHeaderRows = rRng
End Function
Private Function CorrectForTotalRow(rRng As Range) As Range
'rRng.Select
Dim tmpRng As Range
Set tmpRng = rRng.Range(Cells(1, 1), Cells(1, rRng.Columns.Count))
'tmpRng.Select
Dim lCor As Long: lCor = rRng.Rows.Count
Do While WorksheetFunction.CountA(tmpRng.Offset(lCor)) = 0
lCor = lCor + 1
If lCor > rRng.Rows.Count + 2 Then
Exit Do
End If
Loop
If lCor <= rRng.Rows.Count + 2 Then
Set rRng = rRng.Resize(lCor + 1)
End If
'rRng.Select
CorrectForTotalRow = rRng
End Function
rRng.Offset(lOffset).Rangeis looking at whatever sheet and range thatrRngis set to, but within that range you're using justCells(1,1)which is pointing to cell A1 on whatever sheet is currently active. It should be something likerRng.Cells(1,1), or mayberRng.Offset(lOffset).Range(rRng.Offset(lOffset).Cells(1,1),....– Darren Bartrup-Cook Commented Mar 17 at 10:31