I've got a workbook (attached) with a "summary" sheet & 2 detail sheets for tracking vacation time used, and I need to make it automatically clear out an employee's "used" vacation hours automatically on their anniversary date.
Can anybody help me please? I've had no luck thus far & "the powers that be" are really getting on me for this now -
I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.
View 3 Replies View RelatedWhen doing vlookups, I frequently encounter an issue where one column isn't formatted the same and the vlookup won't work.
As an example I am trying to do a vlookup from a sql report downloaded to excel and the formatting on the column isn't right. If I type over the data, the vlookup works.
Is there a way to automatically clear the formatting so the vlookup will work.
Is there way to automatically clear the contents of a cell when the user clicks in that cell. Eg, click in "A1" then then "A1" clears automatically.
View 10 Replies View RelatedIs is possible to clear the contents of a cell range if a message box appears stating an error. I have tried the following code but the logic doesn't actually work and am struggling to achieve what I am trying to do.
My code so far is as follows:
Code:
If ActiveSheet.Range("F84") > 0 And ThisWorkbook.Worksheets("PES").Range("D24") = 0 Then _
MsgBox "Your Entitlement is currently 0", vbCritical, "Error"
ActiveSheet.Range("K84:T84").ClearContents
The message box appears fine if the IF statement is true but if IF statement is false, the cell range of K84 to T84 on the current sheet is still cleared..
I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
rng = Range("F" & Target.Row & ":M" & Target.Row)
If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value = "Yes" Then
Application.EnableEvents = False
rng.ClearContents
Application.EnableEvents = True
End If
End If
End If
End Sub
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
How do I run a macro atomaticly if the value in the cell is > 3. That is what I have so far:
If Worksheets("Variables").Range("B23").Value > 3 Then
I do not know what I should have before that?
I am trying to Excel VBA for doing an AutoSum function in Excel.I am struggling as the recorded macro is giving me fixed ranges whereas the number of rows would be different each time. What I need that the Delivery Qty in Column C be a Total whenever there is a text "Results" in column B?This should happen for every row with Results as a text.In Excel the Autosum feature should take care of this but I am unable to write a VBA code to do this.
Before
DeliveryAct.goods issue dateDelivery Qty
800205573906/05/20094
Results
800205657806/01/200921
Results
800206322306/02/200910
800206322306/02/200915
800206322306/02/200942
800206322306/02/200940
Results
800206322806/02/200941
800206322806/02/200910
800206322806/02/2009455
800206322806/02/2009410
800206322806/02/2009485
800206322806/02/2009785
800206322806/02/200944
Results.................................
I have 41 Sheet in my Workbook.
Sheet1 Contains all the Data
Now For every Staff I have Four Individual Sheet of "Seg"
What I want is to Copy Data From Sheet1 to each sheet of the Staff according to there Respective Details.
1st record to be copied automatically to Sheet7 (CAS B) in Bought Side Column,
2nd record to be copied automatically to Sheet6 (CAS A) in Sold Side Column
with name of toys in both the sheet.
Split from Copy Rows, Meeting Criteria, From Multiple Worksheets & Append To Summary Sheet
This has been wrecking my head for a week. For the debtors tab it is all of the rows which have the value "Unpaid" for each of the 12 months that need to be copied into the tab. For the invoice I thought there could be a button at the end of each row and when you clicked it, it put the info into the invoice template and then the person could just print it off and resend it. And then do the same for the rest of the invoices.
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies View RelatedThe number of rows you are pasting and then you can select that same number of existing rows and use "insert". This is what I'm trying to achieve:
(before paste)
A
B
C
(after pasting unknown number of rows)
1
2
...
n
A
B
C
Is there really no built-in way to do this?
I am working with a file where I have to clear all the rows where the numbers match. Lets say if there is a row in which /in the amount column/ there is a number: 53 and have another row where /in the amount column/ the number is -53 I can match these two rows and put them to a new sheet. So what I would like to have is something which sorts out all the rows in which the amounts can be matched out. Like 53 and -53, 23587 and -23587, 98 and -98. Is it possible to have something like this? Sorry for the language..
Is there some vba code that will completly remove all rows after a certain date in A column, the date is in B2. Dates are listed in A column.
When i say completely remove i mean delete, clear colours, remove borders,etc etc
I have a macro that copies data into another sheet (OR) from sheet1 (EDS) but as I don't want the data added from the last row I want it to overwrite and clear any data that was there before. However I still want to keep my headings which occupy rows 1 and 2.
This is the code I was using...
Sheets("OR").Select
Rows("2:30").Select
Selection.ClearContents
Sheets("EDS").Select
This is pretty inefficent but it works. I don't want to make a new macro I like keeping it inserted in my current one. However I couldn't figure out how to get it to delete the rows past row 2 (it could go to infinity but I don't think there will ever be more than 30 rows of data).
Small piece of code to clear the contents of
Rows 33 thru 100 , then
Rows 142 thru 209 , then
Rows 251 thru 318 , then upto
Rows 43524 thru 43591 then finish
I am working on some code that I would like the user to be able to re-run should the underlying data in the adjacent tabs change. My existing code is shown below
Code:
Option Explicit
Dim wb As Workbook
Dim ws As Worksheet
[Code]....
I'd like to be able to remove all data on the ALL tab so that when the UsedRange.Rows.Count is run again on that page it will only see the header row. In the past I've deleted or cleared data from the sheet and the Rows.Count still counts the rows which have no content in them.
I am trying to return the range of cells that is not in a range of cells.
Dim rge As range
Set rge = activewindow.visiblerange
'set rge2 = cells not in rge 'HERE IS WHERE I NEED HELP
rge2.clearcontents
I am trying to avoid a for each style loop.
In my code I am searching a spreadsheet for certain states and deleting rows that have states I want to exclude. Is there a way to do this with a list of states, instead of having to make many for loops?
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "PA" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
For i = lastrow To 2 Step -1
Cells(i, 4).Select
If Cells(i, 4).Value = "TX" Then
Rows(i).Select
Selection.ClearContents
End If
On Error Resume Next
Next
I have a spreadsheet containing 100 "requests" which can be made by a user
i would like the option for the user to be able to delete a request should they wish it either by deleting a row or clearing the contents
Currently I have a single column which is full of buttons (one for each row)
when i click the button on row 10 I would like row 10 deleted
when i click the button on row 30 i would like row 30 to be deleted etc
is there a way to do this without writing 100 macros which are specific to each line?
I have a 7 (columns) by 2 (rows) grid of cells which I need to perform the following,
If cell a1 is populated, then it will clear the contents of a2, or if cell e2 then it will clear the contents of e1. Basically if any cell is populated it will clear the other corresponding column entry.
I came across this code which works well for one cell
...
I would like an easy/fast way cut an entire row of data from one worksheet ("Open Work") to another ("Closed Work"). Each worksheet has a matching Header Row from column A - N. In "Open Work" I have column "K" with various values, but I'm looking specifically for the "CLOSED" value. If a cell in column "K" = CLOSED, I want to cut the entire row and paste it into the other worksheet "Closed Work".
That was a no-go! It copied limited rows AND duplicated them.
VB:
Sub copyrows()
Dim tfCol As Range, Cell As Object
Set tfCol = Range("A2:A9") 'Substitute with the range which includes your True/False values
For Each Cell In tfCol
[Code] ......
I have an excel file with thousands of rows and I'm trying to pull certain data from one tab to another. In the example, I want to pull all Listings that are "Open" (I know, just filter), but if the listing is "Open" I want to pull all of the other statuses it once had as well in the new tab. Notice how a listing can have multiple statuses.
I was thinking I could filter by Status (Open) and then do a vlookup (using the listing number as the lookup number), but it doesn't work.
I'm working this spreadsheet and i want to automate of deleting the entire rows once the last two columns contains both Zero values. the worksheet cotains thousands of row to clean-up.
View 3 Replies View RelatedI want a macro to delete the entire row based on the value in Column Q ....
View 14 Replies View RelatedI have data in the format below. I'm looking for a piece of code that will look down column B and if there are 30 rows in which all the values are zero then delete all rows from row 1 to the last row of those 30 rows.
Input_data AB106/01/2012 13:03:170206/01/2012 13:03:1816306/01/2012 13:03:1916406/01/2012 13:03:2016506/01/2012 13:03:2117606/01/2012 13:03:2224706/01/2012 13:03:2316806/01/2012 13:03:240906/01/2012 13:03:259.51006/01/2012 13:03:26191106/01/2012 13:03:27211206/01/2012 13:03:28161306/01/2012 13:03:2981406/01/2012 13:03:3001506/01/2012 13:03:31571606/01/2012 13:03:32801706/01/2012 13:03:331091806/01/2012 13:03:341331906/01/2012 13:03:35156
I want to get some blank rows in a worksheet after certain characters.i have some data in a sheet. at the end of each cluster of data it ends either with 1-0 or 0-1.
if excel finds 1-0 or 0-1 at the end of a bunch of rows, it should add 5 blank rows under it.then it should search for the next 1-0 or 0-1
If data ends on A25 where you find 1-0 or 0-1 then add 5 blank rows under it from A26:A30,likewise it should be given till it reaches the end of the range.range can be defined as A1:A1000
I need macro to perform the following steps. I have also enclosed the image of the file for reference.
1)I need to move or copy the data in sheet one to sheet two.
2) In sheet two I need to select customer BBBB and delete all the rows beginning with PO# of 17, 18, 46 or GL4.
3) finally I want to delete all the rows in the sheet which are not equal to class Invoice. (Presently I do this using custom filter as "doesnot equal" = Invoice") ..
Currently i have this to delete entire rows that have cells which contain any words with "Security Market" in it.
How can i extend this to include many more variables like e.g. " Total" "ABC" "XXXX"
Dim rag As Range 'Rows with "Security Market"
Dim sec As String
sec = "Security Market"
Do
Set rag = ActiveSheet.UsedRange. Find(sec)
If rag Is Nothing Then
Exit Do
Else
Rows(rag.Row).Delete
End If
Loop
