VBA how to move cells to bottom of row

JamesonCreevey

New Member
Joined
Mar 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I have absolutely no knowledge with Excel VBA and was tasked with fixing this Excel sheet at work. I am completely stumped and have tried just about everything so far to get it to work.

I have an import button for other Excel sheets that our emissions team creates for me to import to this specific sheet so we can check data and what not. The import function works and all of my If Then statements seems to work as it will always import into the correct sheet based on the data it has in specific cells etc... But we have about 5-8 different sheets so my boss would like for us to be able to import from any sheet on any line and automatically move the cells to the bottom of the correct sheet. The last part is where I am struggling, I can't seem to move the cells to the bottom. If I import from line 7 in a different sheet it will import to the correct sheet, just at line 7 instead of the bottom which causes a lot of issues. I know it seems like a lot but I'm sure it's a simple fix. Here is the code I used to try to fix this issue.

VBA Code:
Dim row As Variant
row = 6

Do While Cells(row, 1).Value <> ""
Cells(row, 1).Select
row = row + 1

Loop
Cells(row, 1).Select

I've been searching for days and have tried just about everything but it still doesn't seem to work. So that's why I'm here, any help would be greatly appreciated as I am about to rip my hair out from this.

P.S sorry if this has been asked a million times, I tried searching for an answer but couldn't seem to find one.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
I'm not sure if this will help but you don't have to loop to find the first available row at the bottom of the sheet. This code copies cell A1 from Sheet1 to the first blank row in column A of Sheet2:
VBA Code:
Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)
 

JamesonCreevey

New Member
Joined
Mar 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm not sure if this will help but you don't have to loop to find the first available row at the bottom of the sheet. This code copies cell A1 from Sheet1 to the first blank row in column A of Sheet2:
VBA Code:
Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)
Thanks, I will give it a try! I appreciate the help
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top