Finding Next Blank Row!

[DanBanez]

Hi Guys,

I am an Excel beginner and I need help!

I have an Excel worksheet that contains 1,000 rows. I already have entered records on some of the rows. Is there a way that Excel will find the next blank row to input data?

Hope this makes sense!

Thanks in advance.

Dan Banez

 

[TiaXL]

Hi Guys,

I am an Excel beginner and I need help!

I have an Excel worksheet that contains 1,000 rows. I already have entered records on some of the rows. Is there a way that Excel will find the next blank row to input data?

Hope this makes sense!

Thanks in advance.

Dan Banez

it depends how your data is set up, if the data is in row after row with no gaps a simple

selection.end(xldown).select
activecell.offset(1,0).select

will take you to the next blank row in the column you have selected

or you can do it in a loop

sub loop()
 
do
 
activecell.offset(1,0).select
 
loop until isempty(activecell.offset(1,0)) = true
 
or 
 
loop until activecell.value = ""

any of those will work

 

[DanBanez]

Hi Tiaxl,

It works!

Thank you very much!

Dan Banez

 

[TiaXL]

happy to help!

 

[DanBanez]

Hi Again,

After the macro found the last blank row, let's say, 499, and I want to automatically delete Row 499 - Row 1,000. how do I code that?

Thanks again!

Dan Banez

 

[TiaXL]

Hi Again,

After the macro found the last blank row, let's say, 499, and I want to automatically delete Row 499 - Row 1,000. how do I code that?

Thanks again!

Dan Banez

range("499:1000").select
selection.delete xlshiftup

if

 

[DanBanez]

Hi TiaXL,

Thanks for the quick response!

It will work, but if I use that code, it will always go to the range 499-1000. But what if the last blank row is not 499 and let's say the last blank row is 525, then the code won't work.

What I was hoping for is a code that when the macro found the last blank row, it will delete that row, whatever it might be, all the way to row 1,000.

Thanks again.

Dan Banez

 

[Trevor G]

Dan,

Are you trying to delete a second table? ie the first table from Row1 must remain everthing else after the first empty row must be deleted? Or is there more to this?

 

[DanBanez]

Hi Trevor,

Here's what I was trying to do:

I have a spreadsheet that contains names, addresses, etc. of donors. The spreadsheet has 1,000 rows, although it has only 475 donors. I have 1,000 rows because I don't want to keep on adding rows when I add donors. There must be an easier way, but I am new to this.

When I query donors, and let's say the query produced only 300 names. These names will appear on my "output range" which also has 1,000 rows. Also, these names will appear on my "merge files" which also has 1,000 rows.

Since I don't want to mess up my "output range", I want to delete rows 301-1000 from my merge file, which I use to "merge" with my MS Word document that I send to these donors. If I don't delete rows 301-1000, they will merge with my MS Word document and produce 700 blank documents.

Hope this makes sense!

Thanks.

Dan Banez

 

[Sektor]

Sub Deleting()
Range(Cells(Range("A1").CurrentRegion.Rows.Count + 1, 1), Cells(1000, 1)).EntireRow.Delete
End Sub