Not sure if I understand your question correctly, but if you wonder why the function loops through all rows, it's because you have two nested loops and you break only from the first then the value is null.
If you only want to go through the range that has actually been used, try using
Worksheet.UsedRange property (Excel) | Microsoft Docs[
^] . Note that this will still return cells with no value if they are within range. For example if you have data on rows one and three the range also contains row two.