VBA - Insert New Row At Bottom Of Dataset And Add 1 To Top Cell?
Mar 25, 2014
Ok, so I have a dataset with 37 rows, column A labels each row 1-36 (labels in the first row) and data goes from column A-G
I have a sub that allows users to fill in some message boxes and answer a few questions so they can insert their own new data to the dataset. I want this data to go at the bottom of the entire dataset (so starting at row 38).
What i'm thinking of doing is, since the A column labels each row numerically, is to get a way to get VBA to take the cell in the A column above the new user-inputted data and add 1 to that number of the previous dataset (So take number in the above cell (36), add 1 (37) and insert that into the new A cell of the new row) just because I have an order function as well) then I would also have to fill the remaining cells with the information the user inputs into the message boxes.
From time to time a have columns of numeric data that I want to sum on the last row. Occasionally, I like to sort Top to Bottom. I have always wondered how to stop the bottom row or total column from moving directly to to the top?
Note: Split from: VBA to insert page breaks at a blank with varying blck size
I would like my macro to add empty lines to each pages not completely filled. (you know when it removes a block from a page, there's always some free space at the bottom of that page) Is it possible to add blank row (from a certain template on another sheet that I have) until it's full?
I downloaded a massive dataset in .csv format to work with. My first problem is that I'm having trouble sorting the dataset. When I open the .csv-file every bit of information comes up in the A-column instead of having INFORMATION1 in the A-column INFORMATION2 in the B-column and so on.
I would like to know if there is a way to expand the selection of cells from the current cell you are on to the very bottom cell available in the spreadsheet.
For example, I want to apply a formula from A12 - the very last A cell row in the spreadsheet.
I am trying to write a macro that will make the bottom cell in a column equal to the top cell. The top cell will not always be in the same row and there won't always be the same number of cells.
I want to be able to call the bottom number in a set of data that is ever expanding. I don't really know how else to describe it , so I will give an example.
Say I have: 3 6 4
I would want to be able to call 4, but then if I had: 3 6 4 5
I would want to be able to call 5, and so on. Is there any simple way of doing this? I basically want the maximum cell name, and the corresponding data inside of it.
Is there way I can copy the BOTTOM cell amount in a specific column, to another specific cell in another workbook? I update a worksheet with a new row of data each day. I want to copy only the last (bottom) entry from one column over to a worksheet in another workbook. I'm hoping I can just put something in the cell I want to copy to, to reference the bottom cell with data I'm copying from.
On some of my worksheets, the slider bar on the right has become a small square rather than a long rectangle. This causes this slightest shift downward to jump through large amounts of rows. Excel seems to think there must be data down there I need. How do I get rid of empty cells at the bottom of a worksheet so that this no longer happens?
I want to add borders to a range depending on the value in the "a" column. Normally I want a left border in cell "a", a right border in cell "h" and bottom border in cells "a" to "h". However if the value in cell "a" is the same as the cell below I do not want the bottom border.
Cell "a" contains the names of customers using our facilities each day. Some customers use several facilities on the same day. I want the borders to encompass all facilities used by each customer per day e.g. range "a1:h8" might need bottom borders on all rows, because they are single customers using only one facility that day but ranges "a9:h11" would require bottom borders only on row 11, because that customer is using three of our facilities that day.
Set Rng = ActiveSheet.Range("a:h").SpecialCells(xlCellTypeConstants) change the Range to "a:h" and:- With .Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeRight)..........
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
I have two problems sort of rolled into one. I have a spreadsheet with a list of skills to perform in cells a2:z2. Under those I put in dates where those skills are performed so a3:a100 , b3:b100 and so on. At the moment I have the these dates populating another sheet with the list of skills going a2:a200 and the dates in b2:b200.
Annoyingly I have been asked set it up so it inputs the other way so my problems are: The skills in the second sheet are in a different order than in the first sheet so its not a straight correlation between the two so I would need to search for the title and match it to the second sheet. Then the second problem is as the will be numerous entries I need to find the next available space in the first sheet. So can you add data at the bottom of a list??
I have used a formula to achieve this before which seemed to work although now it seems to be replacing other data already in the worksheet so I am unsure of how to fix, thinking I should just start again.
The file in question is a tracker of activities, Column A (sheet1) is the Status and once the status is "Closed" I need to run a macro that copies all closed activities to Sheet5 at the bottom of previous closed activities. Once this is done I already have a macro to delete all "closed" activities from Sheet1 meaning that it is vital the new closed activities stack onto the bottom of previous closed activities.
The Column Headers start on Row 11 with activities running from Column 12.
Trying to add a vlookup to a data set and cannot remember how to do.....
I have a table with State and Suburbs. I want to look at both these within another sheet to ensure the post code is correct......
In the attached I want to show in Sheet1 Column D the post code from Sheet2. As there may be suburbs with the same name within Sheet1 I need to ensure the correct Postcode for the suburb and the state.
I have a grid that I need to lookup and return the project hours for each system from the data set. The grid has the projects listed in the rows and the systems listed across the columns. The data set has a list of each projects systems hours. I am having problems with the formula when the data set has multiple records (multiple systems) for each project. It seems very simple, but I just can't get my arms around it. Please see the attached for example.
I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
I was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.
I have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
I have to do a search on a dataset (about 3000 entries) and part of the exercise is to look in some project titles if there is a country or other key words included.
I have look on the web and unable to find a formula that works. Basically what I want to do is to have a formula a looks in the column of titles for a list of keywords on another sheet in the same book.
a macro (that i will link to a control button) that will autofilter a data set. The problem i have is the macro i wrote below, It might not be the best looking macro in the world, but it would work for my purposes if i can get the part that does the autofiltering to be more dynamic. meaning, instead of a hard coding "Retail" in the macro, id like it to reference a cell so that the user can type whatever they want, then click the button and it will filter based on what they type in.
this is what i have Sub Filter_Button() With Sheet2 AutoFilterMode = False Range("A6:M6").AutoFilter Range("A6:M6").AutoFilter Field:=2, Criteria1:="Retail" End With End Sub
I want to create a simple data base of times but it must be as I have in col A .
I am not able to change from using these : .
I have tried scrolling down but when I get to .99 it rolls over to .100
My data starts at 44.00 seconds and wanting to go up to 9:00.0 minutes .
GOOD A1TIME259.97359.98459.9951:00.0061:00.0171:00.0281:00.0391:00.0410 All the way up to 1:00.97111:00.97121:00.98131:00.9914 Then next is 1:01.0015Then next is 1:01.01 16Then next is 1:01.0217 Then next is 1:01.0318Then next is 1:01.0419Then next is 1:01.0520 21 Wanting to go all the way to 229:00.0 and end there
I am looking for a macro that is capable of deleting all rows in a dataset where the following statements are not true: column B is equal to "OP00" (o, p zero zero), the left hand character of C is equal to "L" and D is equal to "CC", as in the scenario below. I basically need to keep all rows which match the structure below, i.e. to clean the data.
B C D OP00LKAOCC OP00LMRPCC OP00LVOFCC OP00LVOFCC OP00LVOFCC OP00LVOFCC
I need to capture the first date that the data appears for a long list of SKUs. I've downloaded the data to have SKUs in a row and dates in the column (consumed over 200 columns).
Would there be a systematic way to generate the first date the data appears instead of having to look at each row, get to the nearest data by using shirt right arrow and then manually type in the corresponding date?
I have 26 data points, and I want to be able to change those data points by a certain percentage, defined by a separate single Cell value
Now here's the tricky part: I want some data points to go up by the defined percentage, and some data points to come down by the defined percentage. Which data point goes up and which comes down, can be totally random.
Is there a way of doing this by means of a formula, or is it a VBA job?
First column should be day; 2nd column should be month; 3rd column should be time; 4th column should be value.
the dataset is arranged like that.
every row is one hour. but the dataset doesn't start with 0:00 am, every day starts with 8:00 am. so the dataset ends with 7:00 am. first data are the months january, february, december of one year(winter). after that those three months from the next year follow. this goes on 30 years. after winter follows spring(march, april, may). also for 30 years. then summer(june, july, august) and autumn follows, for 30 years too.
i have many different datasets. but everyone is about 30 years (262944 rows depending on the intercalary years).
now i would need a macro which adds the years and sorts the dataset. but i need to choose the years by my own(30 years from 1951-1980, or from 1961-1990, 1971-2000... and so on).
