How to create a dynamic range if the last cell is empty and the columns have empty cells

**peter_swe** · 08-03-2016, 07:07 PM

Hi again!
I want to create a dynamic range in column K. The static range is Range("K2:K821")
The dynamic range I then use for counA and CountIF
Since the last row in column A always is nonblank, i thought using offset

Sub test()
Dim ws As Worksheet
Dim rng1 As Range
Dim lr As Long

Set ws = ThisWorkbook.Sheets("lista")
lr = ws.Cells(Rows.Count, 1).End(xlUp).Offset(0, 10).Row
Set rng1 = ws.Range("K2" & lr)
MsgBox "Last Row: " & lr
MsgBox WorksheetFunction.CountBlank(rng1)

End Sub

the last row is correctly set to 281, but when I put it in the range, then select row 2821 and only 1 blank cell
please help me out
Thanks in advance
Best regards
//Peter

**peter_swe** · 08-03-2016, 07:13 PM

It seems like the range

lr = ws.Cells(Rows.Count, 1).End(xlUp).Offset(0, 10).Row
Set rng1 = ws.Range("K2" & lr)

is wrongly written because when i rewrite the code

lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set rng1 = ws.Range(ws.Cells(2, 11), ws.Cells(lr, 11))
MsgBox WorksheetFunction.CountBlank(rng1)
rng1.Select

it works like intended
How should I construct the range code correctly?
Another question: why do not countA

Set rng2 = ws.Range(ws.Cells(2, 10), ws.Cells(lr, 10))
rng2.Select
a1 = Application.WorksheetFunction.CountA(rng2)
MsgBox a1

give the right answer, when the right range is selected? the answer I get is 32 when it should be 30. which is exaktly what this code

1 = Application.WorksheetFunction.CountA(ws.Range("K2:K821"))

gives

**TMS** · 08-03-2016, 07:17 PM

Set rng1 = ws.Range("K2:K" & lr)

**peter_swe** · 08-03-2016, 07:45 PM

Thank you TMS you saved my brain

Best Regards
//Peter

**TMS** · 08-03-2016, 11:18 PM

You're welcome. Thanks for the rep.

How to create a dynamic range if the last cell is empty and the columns have empty cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to create a dynamic range if the last cell is empty and the columns have empty cells

Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

Re: How to create a dynamic range if the last cell is empty and the columns have empty cel

Thread Information

Users Browsing this Thread

Similar Threads

How to create a dynamic range if the last cell is empty and the columns have empty cells

Sum up till next empty and to have another cell with non-empty follow the same range

[SOLVED] Colour fill of empty cells in a dynamic range

MACRO to empty a range of cells based on whether specified cell is empty

Select dynamic range based on non-empty cells

check if any cell in a dynamic range is empty

Finding next empty empty cell in a range of columns

Bookmarks

Bookmarks

Posting Permissions