I am looking for a macro that will remove any rows exceeding 12 months. Each month new data is added in and I am looking to remove anything over 12 months with the new month is added.
View 6 RepliesI am looking for something to calculate values based on the past three months. I already have written a macro to retrieve all of the data and just need this as an add-on.
for example, lets say it is 5/31/2012. I have a row with dates going like
10/31/2010 11/31/2010 .........
I want the formula to calculate SUM(march+april+may row 68)/SUM(march+april+may row 59)
I have an input tab where I can select the month, say 5, and get the periods.
3/31/2012, 4/31/2012, 5/31/2012
if I changed the 5 to an 8 it would get 6/31/2012 7/31/2012 8/31/2012
I don't know if i need VBA for this but I am trying to have something to calculate this depending on the period selected in the input tab.
I am using excel 2010. I am thinking something with an offset function and maybe a loop? but still not sure.
I'm working on a sheet with about 10,000 rows and 8 columns worth of data. Most of them are formatted as 12345,12345,12345,23456 how they are supposed to be, but some of them will have spaces inbetween each sequence, or some even a couple spaces, or spaces at the end. For the most part I can use the replacement function with ", " to "," but some of the double spaces throw it off, and end spaces also. Is there a macro that can just search through the selected cells I pick to just remove all spaces so the data falls back onto the commas?
Working in excel 2010
I'm having a hard time making this maro work in Excel 2010.
I need it to filter out the items "AR", "BATCH", and the line of "Total:*" where the * is a total amount of any given number dependant on the day.
Below is the coding I have that Excel is not liking.
Sub FilterAccurateRawData()
'
' FilterAccurateRawData Macro
'
'
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AA$45415").AutoFilter Field:=1, Criteria1:=Array("<>AR", "<>BATCH", "<>Total:*")
Operator:=xlFilterValues
Sheets("Instructions").Select
Range("A9").Select
End Sub
I'm in Excel 2010, and the cell with the date I want to work from is G22e?
EDIT: I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days. (I also meant H22 not G22)
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
EDIT #2: I guess I should probably note that the date in H22 is the result of another formula
=EDATE(G22,12)
Excel 2010. I set up a trial balance as following:
1/31/2000 2/28/2000 3/31/2000 ------- 12/31/2012
Acct # 1
Acct # 2
Acct # 3
Acct # 4
I was able to use pivot table to set up the balance sheet and income statement.
1. now each month is a field which I can choose to add to the report, but the field list is getting too long (and growing). Is there a way I can insert 1 field (for the user to input the exact date) or 2 fields (to input year and month) and the pivot table can pick up the input and refresh either automatically or manually.
2. I looked into the "group" function in pivot table, but how do I group the above data in quarterly and yearly fashion so the user can get those information quickly?
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
I recorded a simple Macro in Excel 2010 to sort data by the first Column, heading "Ref". I have assigned CTRL + e as the keyboard shortcut for this.
My sheet contains a number of hidden rows (as I hide rows when I have finished working on that issue).
When I run the macro, it does sort by the number of Column A, but also, the hidden rows are then shown again.
I realise I could just click on the A-Z sort button to stop this happening, but I really like being able to use keyboard shortcuts, which is why I tried the macro route.
I wondered if it is possible for the macro to ignore hidden rows?
Code:
This is the macro:
Sub SortByRef()
'
' SortByRef Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
[Code]....
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
(Excel 2010): Hide row if cell C in this row is empty.
I've just started using macros and I'm sure there is one for this problem.
I am on office 2010
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say
Cell A2 has a value 20
Cell A3 has a value 3
Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
20 rows beneath cell A2
3 rows beneath cell A3
5 rows beneath cell A4
I have following sheet:
Data from A1: BU1644
I need to check, if in A1:BU1 are any duplicate words.
All the formulas I found deal with finding duplicates downward (like A1:A1000).
Have not seen any formula which works across (from left to right)
Is there an easy way in Excel 2010 either to tag/ and -or remove the duplicate which I could apply and then just copy downward? The formula must work from left to right, because many words repeat downward.
With Excel 2010 a yellow warning bar is shown when opening a file with data connection. I don't want to show this warning to other users who will be opening the file, especially when they won't be able to update anything since the original data is on my computer. The logical way would be to remove or disable the connections before sending the file out. But when removing the data connections through Connection window all the pivot tables are converted to static tables, which is not acceptable. Is there any other solution to this? Maybe use a macro?
PS: I know that these warnings can be turned off though Trust Center, but I am not interested in changing these settings for other users.
I have created a worksheet and password protected, I know the password but now want to send to someone and remove the password protection option instead of them having to use a password to open it.
View 5 Replies View RelatedUsing Excel 10, Win 7, a User Form with list box control, I only need one column about 7 characters long. (At this point my horizontal scroll bar is taking up more room than the list box window so I'd really like to lose it). I don't see any property options for scroll bars to set.
View 2 Replies View RelatedI have a data in sheet1 as below :
Excel 2012ABCDE1Sr. No.PriceSr. No.Price
21234535123453531234623123462341234712123471251234818123911861
234919135011971235023412350234812371251235125912352261235226101
2353271235327111235428123982812123552912355291312382301235630141
[Code] .........
And now looking for output in sheet2 as below :
Excel 2012ABCD1Sr. No.Price1Price2Total
21234535357031234623234641234712122451234818#N/A1861234919#N/A
1971235023423446881237125#N/A259123522626521012353272754111235428#N/A
[Code] ........
The sr.no. codes and price will keep changing in sheet1, it should automatically update in sheet2. Is it possible with either vba or formula?
I have a list that I need to move to another column without spaces. I have it moving without spaces but it is only one item over and over again. I am using Excel 2010 and that may be my issue. I have attached the exact worksheet and formula.
To Buy List.xlsx
Is it possible to remove all gridlines (except in a table) and change the background colour to plain colour (except the table) in Excel 2010?
View 2 Replies View RelatedHow can i use a single command button to do multiple task in Excel 2010.
First Instance I click -- It filters only With Record "Yes"
Second Instance I click -- It filters only With Record "No"
Third Instance I click -- It removes filter.
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 Replies View RelatedHow do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View Relatedcalculate the number of full rows in one data base located on one sheet X to determine how many rows the macro needs to extend on sheet B (sheet B is made only of formulas for data interpretation in sheet A.
View 14 Replies View RelatedCreate a smart macro which will remove all references to the #REF! which is left when rows are deleted. It would have to remove all trace of it from any equation it may be in (i.e. if it was in an averaging equation, it would need to remove the preceeding comma as well:
=average(a1,a2,!#REF!,a4 .........)
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
I am using Excel 2010 and I am trying to hide the rows# 10 to 12 in Sheet6 and it is working perfect by clicking on radio button.
[Code].....
Now i also want to hide two more rows in Sheet7 by using below snipet but it doesn't work.
[Code] .....
I am working in Excel 2010 on a windows 7 machine.
I have a years worth of data by day. I want to sum the values for each week.
For example, Cell B1 is: =sum(a1:a:7)
Cell B2 is: =sum(a8:a14)
Cell b3 is: =sum(a15:a21)
Is there a formula I can write that will increment the numerical portion of the cell by 7? If I drag down cell B1, the resulting formula is: =sum(a2:a8)
I know that I can use either =sumifs or =sumproduct to compare the dates and calculate based on that.
I tried: =sum(a1+7:a7+7) but that returns an error.
Again, don't propose solutions with either =sumifs or =sumproduct since I can already write those formulas.
I have some code that worked perfectly in Excel 2007 but crashes Excel 2010.
The part of the code that appears to be the problem is this:
Code:
For i = LR To 2 Step -1
If Cells(i, 15) = "Delete" Then Rows(i & ":" & i).EntireRow.Delete
Next i
For info., both screen updating and calculation are already both set to manual.
I am looking for some to create a simple macro to sort multiple rows in ascending order based on the values in a particular column in the selected range. I want to sort A4:AI1004 in ascending order by column D. I recorded the following macro in Excel 2010:
VB:
Sub sort()
'
' sort Macro
' sort by column D (Sequence #)
'
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
[Code]...
First of all, I think this code can probably be simplified. Secondly, it does not work in older versions of Excel. In 2007 it always ends in a runtime error. How can I clean this up so that it will work in both versions of Excel?
I am trying to separate two rows to two columns. How can I do this for +200 data. For example,
1
Apple
2
Ball
3
Candy
....
I need this to be:
1 Apple
2 Ball
3 Candy
and so on.
show me how I can do this for Excel 2010.