cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gupta
New Member

Add Row to Excel from a Form on random row instead of next available

We have a Office 365 form which collects the data and then using Power Automate to add row in a Excel table. For some reason the data being inserted to row# 200 on wards instead of next available row which is row#28.

 

Any assistance would be much appreciated!

 

1 ACCEPTED SOLUTION

Accepted Solutions
MichaelAnnis
Super User
Super User

Get Last Row only works if all data/formatting is cleared from all blank cells extended below the bottom of your data.

For instance, if you manually were to open this workbook, and press Ctrl + End, which cell gets selected? My guess is the last column on row 199, which is why it is putting the next available row as 200.

 

To clear all blanks, do this with your bot:

Send Keys {Control}(a) #Select All

On the Home Ribbon, click Find & Select, Go To Special #use the recorder if you need to walk the bot through this

MichaelAnnis_0-1631644837375.png

 

Blanks -> OK #If you can't find the window selector for "Blanks", use Send Keys {Alt}(k) instead

MichaelAnnis_1-1631644901111.png

Again on the Home Ribbon #with all the blank cells highlighted

Click Clear -> Clear All

MichaelAnnis_2-1631645015100.png

Then "Select cell A1"

Then "Save Excel".

 

This will reset the last row, so when you use "Get Last Row" command in PAD, it will actually get the last row of data and not some ghost row down below.

 

Best of Luck!

View solution in original post

1 REPLY 1
MichaelAnnis
Super User
Super User

Get Last Row only works if all data/formatting is cleared from all blank cells extended below the bottom of your data.

For instance, if you manually were to open this workbook, and press Ctrl + End, which cell gets selected? My guess is the last column on row 199, which is why it is putting the next available row as 200.

 

To clear all blanks, do this with your bot:

Send Keys {Control}(a) #Select All

On the Home Ribbon, click Find & Select, Go To Special #use the recorder if you need to walk the bot through this

MichaelAnnis_0-1631644837375.png

 

Blanks -> OK #If you can't find the window selector for "Blanks", use Send Keys {Alt}(k) instead

MichaelAnnis_1-1631644901111.png

Again on the Home Ribbon #with all the blank cells highlighted

Click Clear -> Clear All

MichaelAnnis_2-1631645015100.png

Then "Select cell A1"

Then "Save Excel".

 

This will reset the last row, so when you use "Get Last Row" command in PAD, it will actually get the last row of data and not some ghost row down below.

 

Best of Luck!

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Top Solution Authors
Users online (2,534)