AutoFill Down Rows Based On Another Columns Last Cell
May 13, 2008
I have a workbook which has one sheet "Raw Data" where I will cut and past blocks of data of various numbers of rows. Another sheet "level one calcs" conducts various calculations on the raw data. I was wondering if there would be any way to create a macro which would copy down (autofill) the last row of my my calculation page for the exact number of rows I added into the raw data. The goal here would be so that I dont have to highlight and copy down the formulas on the calc sheet each time I add in new data.
View 3 Replies
ADVERTISEMENT
Oct 28, 2012
I need this solution if possible:
On one part of sheet I have items set in rows : A1= apple,A2=banana, A3=orange etc.
Now I need to set in lets say A55=A1, B55=A2, C55=A3 etc. How to do this in fast way without writing a formula in each cell?
View 5 Replies
View Related
Dec 15, 2008
Example:
Tab where the information is captured:
___A____ B____ C____ D
1 _TP1__ TP2__CON___PT
2 __3____3_____2____4
3 __2____1_____6____5
The tab where I want the info to pulled to runs in a row, not in columns, and there are 6 different branches with 35 different categories that I need to add the formula to to pull the info I require.
So, when I type the formula (=TAB1!A2) in A1, I get the correct info I require, but then when I drag it to the right (A2), it changes the formula to (=TAB1!B2) as apposed to what I want which is (=TAB1!A3). So now I have to manually type the formulas which will take me forever!
View 9 Replies
View Related
Nov 30, 2009
I have searched for this on the site as I thought it would have already been asked but I couldn't find anything. I have a formula in cell C7 which I wish to copy both down to the last row (xlup from column A) and across to the last column (xltoleft on row 6) but I cant seem to work out how to do it. I have used the .autofill before but for dynamic rows only. Is it possible to do for columns too?
View 2 Replies
View Related
Jul 26, 2012
I have wookbook with a lot of sheets on it
The first sheet to be filled is called "Local Info"
In this sheet in the Active Cell "G7" the user must register a number between 0-500
This number must affect my next sheet called Competence Map Autofilling rows 7+8 from Columns A to AM
So If for example I put 83 in "local INFO" G7.
I will get 172 rows with text (the first 6 Headers) the 7-8 original and from 9-172 filled with the information given.
View 3 Replies
View Related
Jan 31, 2014
I am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.
Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.
View 2 Replies
View Related
May 20, 2014
I have a workbook sheet change condition that, when triggered runs the following code (amongst others).
[Code] ......
What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.
So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190
View 4 Replies
View Related
Jul 25, 2012
In cell A12 the format is mm-###. Based on the mm (2 digit month) in cell A12, I would like cell B12 to auto fill the last date of the month as mm/dd/yyyy.
View 4 Replies
View Related
Aug 25, 2012
I want a range to be filled based on 2 cell values in my sheet. The autofill range should be filled based on the values in the input range. For instance in cell A1 I place the value 1 and in cell A2 I place the value 10, then the macro should automatically fill in the numbers from 1 to 10 in another column. If I change for instance the input values to 5 and 15, then the autofill range should fill up the numbers from 5 to 15.
View 4 Replies
View Related
May 25, 2012
I have an Excel file for work that has the following:
Column 1 is Agent
Column 2 is Interval (in 15 minute intervals from a report that I pulled)
Column 3 is Agent Calls (has a number anywhere from 1 - the highest currently is 19, which tells me how many calls that Agent had on that 15 minute interval.)
What I have currently is that if it says:
John Doe 15:00 5
It will automatically insert rows after the number based on a # -1, so it will add 4 empty rows after this row.
I have another step that will automatically copy Column 1 and Column 2 to the blank rows directly beneath them until it reaches a cell with data.
Now, what I need is a macro that will take Column C and where it says 5 automatically know to change that to '5a' then proceed to go down the list with 5b, 5c, 5d, & 5e. If it sees 4 it will know to change that to '4a' then proceed to go down the list with 4b, 4c, & 4d. I currently have numbers ranging from 2 - 19.
I have built custom list with this information so if I change all the numbers from 4 to 4a or 19 to 19a and double click on the black box it will autofill exactly like I want but it only goes down to the next cell with data, and I have 100+ lines per agent, and up to 21 agents a day, so this gets time consuming.
Here's how it looks when I'm ready to start the macro:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:15
JohnDoe - Doe, John15:45 4a
[Code] ......
And I need it to look like this:
JohnDoe - Doe, John15:00 x
JohnDoe - Doe, John15:15 4a
JohnDoe - Doe, John15:15 4b
JohnDoe - Doe, John15:15 4c
JohnDoe - Doe, John15:15 4d
[Code] ........
View 7 Replies
View Related
Aug 25, 2014
how best to do this (bare with me I'm not quite a pro at excel yet!). I'll attach my doc (JMP Excel Forum) so you know what I'm taking about. I've set up the data to work in a similar way to this, as you can see I've a table set up (Appliance Sheet) which feeds information to the Main Sheet which contains the drop down lists.
What I need it to do is autofill the wattage column (Main!F8 onwards) with the wattage for the device as described in Appliance!N3 or N4 for example. For the wattage column to equal 28 (equivalent to Appliance!N3) then the following would show in the dropdown selections Type>Computers SubCategory>Monitors Device>BENQ G2222HDL Status>On
Is there a way to do this with the set up I have?
I did have a previous version of the whole worksheet that each Device had its own table and I looked up the wattage using and If formula to lookup a info on the device and knowing where to look it up based on the subcategory
=IF(ISERROR(VLOOKUP(D7,IF('Main UI'!C7="Heating_Cooling",Appliances!$A$7:$B$14, IF('Main UI'!C7="Kitchen_Bathroom",Appliances!$A$16:$B$27,IF('Main UI'!C7="Monitors",Appliances!$A$52:$B$62,IF(C7="Computers",Appliances!$A$64:$B$68,........
And I got it to work but it was getting to cumbersome to add in new devices and update the formulas. If attached that doc (OLD) so you know what I'm talking about. The last two sheets are the same sheets that make up the new document (JMP Excel Forum)OLD.xlsm
I really prefer the new way I've got it set up. Its easier to add new information or options to the dropdown list
View 2 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Mar 29, 2014
Problem: The following code autofills columns O:P beyond the last row of data in column H.
Range("O3:P3").AutoFill Destination:=Range("O3:P3" & Range("H" & Rows.Count).End(xlUp).Row)
View 3 Replies
View Related
Dec 20, 2009
How exactly do I autofill by columns? In this example I recorded a macro and cleaned it up autofilling through E1 but it could be a number up to 50.
View 2 Replies
View Related
Oct 14, 2011
I need with the below statement. I need the statement to autofill columns b6:y6 down as long as there is data in column A6 down.
I am hitting a wall here
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/14/2011 by Oasis Group
'
' Keyboard Shortcut: Ctrl+Shift+B
'
Range("B6:Y6").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B6:Y" & Lastrow)
Range("B6:Y" & Lastrow).Select
End Sub
View 3 Replies
View Related
Jan 24, 2008
I have got 5 columns. What i intend to do is to loop to the last data cell in column d,e and autofill it down from there till the last row in column a,b,c. What my code does now is to autofill the entire column c,d from the 1st cell and not from the last data entry cell.
Dim r As Range
Dim lRow As Long
lRow = WorksheetFunction.Max(Range("A65536").End(xlUp).Row, _
Range("B65536").End(xlUp).Row, Range("C65536").End(xlUp).Row)
Set r = Range("d1:e1")
r.AutoFill Range("D1:E" & lRow)
I had searched and tried dozens of variations but just can't get it right.
View 4 Replies
View Related
Apr 11, 2009
I need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file ......
View 12 Replies
View Related
Apr 28, 2009
I'm sure this is simple code, but...
I have a data table with over 20,000 rows that refreshes each day. I need to run a bit of code when it refreshes that says if the value in column R is 2, then delete that row.
View 14 Replies
View Related
Apr 28, 2009
I have attached a sample spreadsheet where I now want to move date from multiple rows to one row and multiple columns where data in A is common, hopefully spreadsheet shows this more clearly. I have shown sample on two sheets, before and after. I want to have all data in a single row for instances where A the same.
View 2 Replies
View Related
Jan 20, 2009
I have a worksheet with columns of Unique Words and corresponding Frequency of Occurrence for several years. I would like to sort the data so the rows match up on same Unique Words across years.
I am using Excel 2007.
View 9 Replies
View Related
Oct 9, 2011
I need to delete rows that are blank, 0, or contain errors (#REF, #N/A, etc). My problem is that once in a while, an error will show up in a singe cell of a row that I need to keep so a simple deletion of only errors/blanks/0 will not work for me.
The two cells I need to check for errors, blanks, and 0's are D and E. There can be an #N/A in col D and a 0 in col E or both D and E can have errors. Blanks will occur in all cells of the row.
I have tried to use the code below but it doesn't do anything, but it also doesn't give me an error.
Code:
Sub DeleteAll()
Windows("Template.xls").Activate
Dim r As Long
For r = 2 To 36500
If Range("D" & r).Value = "#N/A" And Range("E" & r).Value = "#N/A" Then
Range("D" & r).EntireRow.Delete = True
[code].....
View 1 Replies
View Related
Dec 22, 2006
I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.
In Column L it has either numbers 1 through 100, or the letter "D".
What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.
Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".
It might be easier to do it separately because once you run the part that deletes the rows that dont have a C or LP then I could probably just do one that says if column L = D then change to 1.
Lets see if I can simplify this for you.
If Column L = D and Column A = BLANK then Delete Row
(this would delete all Ds that dont have the C or LP)
THEN
If Column L = D then change to 1
View 9 Replies
View Related
Sep 21, 2012
two things on the attached sheet.
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
View 6 Replies
View Related
Feb 18, 2010
I need to filter the following file.
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
View 2 Replies
View Related
Mar 18, 2014
I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.
View 1 Replies
View Related
Dec 5, 2008
I have a workbook in which I have two sheets. One sheet is a report and the other is a data dump. The data dump has headers in in column A starting in cell A6 and headers in row 5 starting in cell B5. There is then data going from B6:J20.
In my report I then I have same setup with headers in column A and row 5. The difference is that the headers are not in the same order as the dump. What formula could I use that would look for the two headers in my report sheet and then match it with the value in the data dump that uses the same two headers?
View 2 Replies
View Related
Jan 29, 2013
a code that will search each cell across 4 columns and hide the row only if all cells are blank. The macro should search columns "b", "c", "e", and "f" to display all rows where at least one of the cells has a value.
Ex.
Col.B Col.C Col.D Col.E Col.F
1. 123 xxxxx 150
2. 56 xxxxx 50
3. (blank) (blank) xxxxx (blank) (blank)
In this ex. row 3 would be hidden.
View 1 Replies
View Related
Mar 19, 2007
From a base table, I need to create a new table listing only the unique combination of 2 columns and their individual sum. Example:
Base Table:
ITEM-----Loc-----QTY
Pen------School----5
Pencil----Office-----9
Binder---Office-----9
Pen------Office-----6
Pen------School---15
Binder---School---12
Pencil----Office----10
Binder---School----6
Pen------Office----11
I need to have a table resulting to the following:
Item-----Loc------Qty
Pen------School----20
Pencil----Office-----19
Binder---Office------9
Pen------Office-----17
Binder---School----18
View 9 Replies
View Related
Feb 3, 2009
I need to insert one or two rows depending on the criteria of two different columns.
We have two shops (A and B)...and the sales are expresed like this: ....
View 9 Replies
View Related
Jan 7, 2010
How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.
View 9 Replies
View Related
