- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Macro Help needed... Spreadsheet w row count that changes over time
Posted on 2/18/19 at 8:39 am
Posted on 2/18/19 at 8:39 am
I have a payroll spreadsheet that grows over time (after each payroll) I have been manually sorting it to remove old, unneeded and/or duplicate data and recently wrote a basic Macro that will sort and filter what I want. It works great on whatever sheet I write it in and saves alot of time.
I now have run into the problem that when the spreadsheet has more rows that the one I wrote it in... it errors out. When I tried to debug I can see the row parameters are set statically.
Q) How can I tell the Macro to adjust the range based on what row count is there when the spreadsheet is opened?
I have tried using control /arrow down as the first step to get to the last row cell but it does not seem to dynamically adapt to the spreadsheet as it grows
I now have run into the problem that when the spreadsheet has more rows that the one I wrote it in... it errors out. When I tried to debug I can see the row parameters are set statically.
Q) How can I tell the Macro to adjust the range based on what row count is there when the spreadsheet is opened?
I have tried using control /arrow down as the first step to get to the last row cell but it does not seem to dynamically adapt to the spreadsheet as it grows
3
Posted on 2/18/19 at 9:29 am to Crow Pie
Assuming this is structured in a tabular manner, can you just write a function to find the first blank row in column A?
However, what you're doing sounds best suited for a database solution like Access.
However, what you're doing sounds best suited for a database solution like Access.
Posted on 2/18/19 at 10:51 am to Brisketeer
You can modify the macro to loop down looking for the first blank row, or make a dynamic variable to find the last row using xlUp.
Posted on 2/18/19 at 11:07 am to Crow Pie
use a row count on a column that always has data to get the total size and go from there
Posted on 2/18/19 at 7:09 pm to LSUtigerME
quote:"xlUp "....to set the range of cells?
make a dynamic variable to find the last row using xlUp.
This post was edited on 2/18/19 at 7:12 pm
Posted on 2/18/19 at 8:12 pm to Crow Pie
Use that command to find the last row of data. It goes to the last row in Excel, and looks upward for the last used row.
LastRow = Sht.Range(“A” & Rows.Count).End(xlUp).Row
Or
= Sht.Cells(Rows.Count,1).End(xlUp).Row
This will give you the row number of the last entry in Column A. It’s more reliable than looking down if you have blanks in your data, but of course is vulnerable if you have blanks in the last entry of the column you’re searching.
Whatever the static number that was being used for last row can be replaced by this function.
LastRow = Sht.Range(“A” & Rows.Count).End(xlUp).Row
Or
= Sht.Cells(Rows.Count,1).End(xlUp).Row
This will give you the row number of the last entry in Column A. It’s more reliable than looking down if you have blanks in your data, but of course is vulnerable if you have blanks in the last entry of the column you’re searching.
Whatever the static number that was being used for last row can be replaced by this function.
Posted on 2/19/19 at 1:29 pm to Crow Pie
What about formatting your range as a table. Table references are always dynamic.
Posted on 2/19/19 at 3:37 pm to CubsFanBudMan
Yep, my first thought was make your data a table so it’s dynamic.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News