Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Shift Cells Up If Cell Equal 0


In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up.

Here is the code I tried, but nothing happened:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 25 Then Exit Sub
If Target.Rows > 66 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then Target.Delete Shift:=xlUp
End Sub


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
'shift Cells Right' Depending On Value Of Cell
I need a macro that will "examine" the text of column B for every row checking to see if the text "Proper Text" is in the cell and if not, execute this code for that cell and then move on to perfrom the same until it finds an empty cell:

Selection.Insert Shift:=xlToRight
Range("B2085").Select
ActiveCell.FormulaR1C1 = "Click for detail image"
With ActiveCell.Characters(Start:=1, Length:=22).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic

Note that "B2085" above is the cell number that the script stops on to execute this code.

View Replies!   View Related
Delete And Shift Cells Based On Cell Values
I have a spread sheet that has RX codes as field headers and ones or zeros as values. I want to find all cells which contain "0" and delete those cells while shifting the remaining cells left. The range of cells is aj1:dr17365 and the sheet name is "May 07 Rx HCCs"

View Replies!   View Related
Cell V7 To Tell Me If The Number In U7 Is Greater Than Or Equal To The Individual #'s In Cells U7-U40
I am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?

View Replies!   View Related
Join 2 Cells & Show Equal Sign In Cell
For Exempel

A1=B
A2=1
A3="="&A1&A2

I have even tried with concernate and indirect in A3 but no difference there...

I want A3 to return =B1 but it only returns it in text but i want it in form of a formula. Is there a formula that returns text directly into a formula or an easy way to do this?

View Replies!   View Related
Add Formula To Cell To Equal Product Of 2 Other Cells Via Macro Code
I'm trying to set a cell on one sheet to be equal to the product of two cells on another sheet. The problem is that one of the cells on the other page is dependent on the variable T. This is what I've got and it's giving me errors

Worksheets("Output").Range("K14").Formula = "Worksheets("Calcs").Range("D17")*worksheets("Calcs").Range("D17").Offset(10+T,0)"

When I record a macro it gives me this, but again, I need the last cell in terms of "T"

ActiveCell.FormulaR1C1 = "=Calcs!R[3]C[-7]*Calcs!R[14]C[-7]"

View Replies!   View Related
Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

View Replies!   View Related
Calculate Pay For Shift Work With Different Rates Based On Shift Hours
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

View Replies!   View Related
Delete Cells, Shift Everything Else Below It Up
I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...

View Replies!   View Related
VBA To Shift Cells To The Left
how can i get this

Sheet1

ABCDEFGHIJKL7WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N

View Replies!   View Related
Insert A Value And Shift Cells Value Down
If I put a length in (G13) such as 4.44 & position from (H13) is 1. Then I want that length of (G13) to move its value (4.44) into (C15) the 1st cell to add to is (C15)

Then Move (C15) to (C16) 11.12 becomes C16
and move (C16) to (C17) and so on all the way down the C column.

When it hits the last joint (C374) position 360. I want a messageBox that says you can not insert anymore.

Basically shift the existing values down untill (C374)
I have only column C to shift the calue from (G13)

I do NOT want to delete rows!

Below is how it looks before macro:
GH12LENGTHPOSITION134.441

C14LENGTH1511.121612.221711.561811.861913.112012.872113.062211.992312.03

Below is how it will look after macro: The RED moves to (C15)
C14LENGTH154.441611.121712.221811.561911.862013.112112.872213.062311.992412.03GH12LENGTHPOSITION134.441..........................




View Replies!   View Related
Delete Cells And Shift Up
I have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up.

View Replies!   View Related
Make Cells Shift Down Automatically
i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.


View Replies!   View Related
Insert-shift Cells Down Macro
I want to be able to insert-shift cells down based on criteria in another cell. For instance, I would like a macro that would look at column B for saturday and sunday and then insert-shift cells down on the corresponding cells in column D...

View Replies!   View Related
Delete Row Macro (shift Cells Up Then)
I am looking for a macro that can delete whole rows (shift cells up then). How i would like it to perform is if there is nothing in cell(s) B then delete the whole row?

View Replies!   View Related
Shift Cells Down Depends On Condition Of Others
Whenever "online" appears in the "Desc" column I need to shift the matching row down (shaded area). for example, "online" appears in H2, hence, C2:E2 need to be shifted down to C3:E3 leaving C2:E2 blank. Next, H3 has "online" there, C3:E3 need to be shifted down one row so the result will look like the data on the "result" tab.

View Replies!   View Related
Shift Cells Up, Removing Blank
How do I shift all the cells up labelled data, so that there are no blank rows in between? I tried using the ones found on the forum via search but it is stuck in an infinite loop.

View Replies!   View Related
Cut / Paste And Shift Cells Depending On Value In A:1
I am trying to make a macro to take a set of data and shift the data range down depending on the value in the A:1 cell. For example if the data in column A starts with 5, 6, 7, 8 and so on.... I would like to copy/ paste and shift the column down 5 cells so I can start A:1 at 0.....so that the data can begin as 0,1,2,3,4,5.....etc.

View Replies!   View Related
Delete Cells Shift Left
I'm trying to do is check each Row and move the last 3 Columns of data in each Row to the left so they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W). Columns A and B are temporary. Column A utilizes the formula "= COUNTA(B2:AG2)" to count the number of cells in the row that contain data. Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data in the active worksheet. Columns A and B will be deleted at the end of the Macro.

The GOAL (END RESULT) is for Columns A through W (and ONLY these Columns) to contain data (keeping in mind that Columns A and B will be deleted at the end). If some rows have Column W (and beyond) blank, then I want to MOVE data from Columns U & V over to V & W and then COPY data from Column T into the [currently] blank Column U.

I've been trying to get the Macro to start in the last row and, using CASE Statements, delete the proper range of cells and SHIFT LEFT as it counts backwards towards the first row. I'm not too sure this is the best approach and could really use some advice from the experts! I can provide a "test" file if necessary ... the test file I've been working with is approximately 6.5MB, but I can delete most of the 14,287 rows and still give a good representation of how the data varies.

Sub b_DeleteCellsShiftLeft()
' DeleteShiftLeft Macro
' Macro recorded 3/14/2007 by George Nicholaou

' Need to assign variable for current row (?)
' r=ROW()

' Columns A and B are temporary
' Column A utilizes the formula "=COUNTA(B2:AG2)" to count the number of cells in
' the row that contain data
' Column B utilizes the formula "=COUNTA(C:C)" to count the total rows of data
' in the active worksheet
' They will be deleted at the end of the Macro
' What I want the Macro to do from this point is:
' 1. Check each Row and move the last 3 Columns of data in each Row to the left so
' they fall under the headings NHA2, NHA1, and OEM PN (Columns U, V, and W)
' 2. The GOAL (END/RESULT) is for Columns A through W (and ONLY these Columns)
' to contain data (keep in mind, Columns A and B will be deleted at the end)
' 3. If some rows have Column W blank, then MOVE data from Columns U & V over to V & W............


View Replies!   View Related
Shift Cells Left Based On Text Value
I need assistance in shifting cells to left based on three (3) specific text values in column B. There are tons of data and extra lines and stuff but consistently these three (3) text values are consistently not aligned and I simply need to shift entire row 1 space to the left. I can do the long and tedious process of going through them all but is there a macro which I can play that does it instantly...

View Replies!   View Related
Delete Empty Cells, And Shift Left
I am trying to find a method for deleting empty cells in a range and shift cells left upon deletion automatically. I could do this manually, but with 3000 rows of data it could be a long task. I have attched a small spreadsheet to show what I am looking for.

View Replies!   View Related
Import Data: Cannot Shift Nonblank Cells
I've got a problem with one of my macros. This code should import a text file. The name of the imported file is the same as the value of the cell that is active the moment the macro starts. The way I've written it, the code can't find the needed text file.

Sub addfile()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:aprojects" & ActiveCell.Value & ".txt", Destination:=Range(ActiveCell _
))
.Name = ActiveCell.Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0.........................

View Replies!   View Related
Delete Empty Cells And Shift Remaining Left
I am looking for a method which would delete empty cells in a row, and then shift the remaining data to the left (filling the cells with data). I know that this could be done manually, but I need it completed for 3000 rows! I have attached a simple sheet to show what I'm Looking for.

View Replies!   View Related
Cannot Shift Nonblank Cells Off The Worksheet When Inserting Row/column
When I try to insert a row, Excel displays the warning "cannot shift nonblank cells off the worksheet". I understand what the warning means but there are no nonblank cells at the bottom of my worksheet! Just to be sure, I have selected the bottom-most rows, cleared them, deleted them, and everything else I can think of. The warning still appears.

View Replies!   View Related
Shift Range Of Cells To Calculate Formula By Fixed Number
How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??

View Replies!   View Related
Macro To Search Column For Blank Cells &amp; Shift Entire Row Right
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).

View Replies!   View Related
Search Cell If Blank Delete Cell Shift Values Up
The below code is what I have and it works but what I need is for it to loop until it doesn't delete anything. How can this be done?

View Replies!   View Related
Equal Value & Formatting Between 2 Cells
Is there any way to make one cell on a separate sheet appear EXACTLY the same, including formatting and values?

As in this for example:

Pieces of Paper (Blue)

I would like to keep the formatting for (Blue) , color and bold, but I have not figured out how to do this.

View Replies!   View Related
Run Macro Only If 2 Cells Are Equal To Each Other
I have a macro to insert a row and I would like it to be triggered by conditions in a IF statement or whatever is best? Macro has a ctrl+shortcut.

eg =IFa10=a12
do nothing
or run macro


View Replies!   View Related
Why Aren't These 2 Cells Equal
I have a date on one sheet, and what I thought was the same exact date on another sheet. I want to vlookup off the date, however the values aren't the same. i.e. when I check to see if they = I get a false. They're both formated the same, custom date m/d/yyyy h:mm, and when I ran the check to see if one was text (t=(A1)) I get confirmation that they are both non-text!

View Replies!   View Related
Average If Cells Are Equal
Columns L, M and N are always fully populated.

Columns D, E and F may have some missing values, along with H, I and J.

Lets just concentrate on D.

I need to create a formula where I get the anser in C1 This is an average of L only where D has a value.

View Replies!   View Related
Select Cells Equal To Zero
How can I select all rows where certain cells are equal to zero?

(i.e. in Column A

1 Bob
2 Joe
3 Smith
4 0
5 0
6 0

I want to select rows 4, 5 & 6.

I've used Go To>Special:Errors to write a macro to hide all rows with errors, but I also want to hide all rows that have zero values

View Replies!   View Related
Hide Row If Three Cells In That Row Each Equal Zero
I am trying to hide rows if cetain cells in that row equal zero using a button on the page. Each cell has lookup formulas that will return a value. If coulmn B,F & I have a value of zero I want to hide the row. As of right now I am using the following code but I keep getting the following error message runtime error '13': type mismatch

View Replies!   View Related
Cells That Appear To Be Equal, Aren't
I have 2 text cells (A1, B1) that appear to be the same, however, when I typed the formula "=if(A1=B1,"Yes","No)" in cell C1 the result is NO! (see attachment)

View Replies!   View Related
Compare If Multiple Cells Are Equal
I need to compare the data in three fields and if the same ignore but if different then add them with a space and a hyphen.

I use this formula for 2 fields and it works great I just can't get it to work with three.

=IF(TRIM(Data!BC2)=TRIM(Data!AY2),TRIM(Data!BC2),TRIM(Data!BC2)&" - "&TRIM(Data!AY2))

I use the trim command as the users entering data often add unnecessary spaces etc. and it combines the data even when the same.


View Replies!   View Related
Comparing 3 Cells As Equal Or Unequal
Columns E and F can have a 1 or a Zero
If e2, d3, e4 = all 0's or all 1's, count = 3 in col F
If either e2, d3 or e4 is not equal the count is 2 in F

Example

D E F
0
0
0 3
0 1 2
1 1 2
1 1 3

Also, why would this formula work with numbers but not letters?

=IF(B2="x",0,IF(C2="x",1,""))

View Replies!   View Related
Identify Which Cells Equal A Total Value
Is there a formula or macro that will identify which cells on a spreadsheet containing various dollar values will add up to a predetermined total? I work in Accounts Receivable and sometimes vendors send in payments for several invoices with no instructions as to the application. I want to be able list the available invoice amounts on a spreadsheet, and be able to tell which of the cells add up to the payment amount.

View Replies!   View Related
Compare Cells & If Equal Perform Function
I need to vba help on the following:
For 1st A1 to Lastrow
{
if cells are equal
{
F1 = (C1+E1) - (B1+D1)
}
}
below is the records that i'm trying to worked on..has about couple thousands rows...
Description Debit Credit Debit Credit Profit
74HBHZMPXC 3,321.00
74HBHZMPXC 3,321.00
74HBHZMPXC 10.00 10.00
75HBCTIWSI 30.55 30.55
75HBJWFZEU 1,095.60
75HBJWFZEU 1,095.60
75HBJWFZEU 2,198.00 8.00
76HA25FYGB 925.00
76HA25FYGB 925.00
76HA25FYGB 1,856.00
76HA25FYGB 1,856.00
76HA25FYGB 1,856.00 6.00

View Replies!   View Related
Ding Sound For Cells With Equal Values
how to apply this code to include specifically these ranges in pairs: H50 & H51, H102 & H103, H154 & H155, and H206 & H207.

'tried to cut and paste the code from Private Sub to End Sub changing only
the Ranges (see above), but it didn't work.

View Replies!   View Related
Set 2 Cells Equal, While Allowing Either One To Be Manually Changed
Is there a way to set 2 cells equal, while allowing either one to be manually changed? So if I put in value of 10 for Cell1, Cell1 and and Cell2 will both be equal to 10. Then if I change Cell2 to a value of 20, both cells will be equal to 20.

View Replies!   View Related
Compare Two Cells But Accept Them As Equal Is One Is Plus/minus 0.1
I'm trying to create a formula where I can compare two cells but accept them as equal is one is plus/minus 0.1.

Example:
Cell A1 is 3, Cell A2 is 3.2, I want Cell A3 to turn red (or report "1")
Cell A1 is 3, Cell A2 is 3.1, I want Cell A3 to turn green (or report "0")


View Replies!   View Related
If Not Equal To: Test If The Value Of Cells AZ2:AZ2000 Are MB50017
Collumn AZ 2:2000 should always have a value of MB5017 in the first 6 positions on the left, with an additional four digits following. I need to test if the value of cells AZ2:AZ2000 are MB50017 - leaving the variable last four digits. If not than replace them with "MB50017"

Column AZ

MB500174443
MB500174446
NB500176554
VB401038789

The first 5 characters should always be MB50017 plus the last four digits
The column should then look like:
MB500174443
MB500174446
MB500176544
MB500178789

View Replies!   View Related
Date And Time Based On Shift In Cell
If I use =now() in a cell it will enter the current date and time. For shift workers, this does not work.

I'm tring to figure out a formula that will only change the date at 6:00am every day. This way a person working the night shift will not see a change in the date on his sheet at all, only the day worker.

View Replies!   View Related
Conditional Formatting :: A1 Is Equal To Data Highlight Cells On A2
how to create a conditional formatting formula that looks like this..

"If A1 is equal to DATA highlights greater than zero on cells A2 to A1000.

View Replies!   View Related
Draw An Arrow Equal The Size Of The Selected Cells
I want to select some cells (like A2-A20) and have it draw an arrow down the selected cells. Tried a few different things, none of which worked...

View Replies!   View Related
Start Shift And An End Shift
I have a Start Shift and an End Shift time,

Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs

I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.


View Replies!   View Related
Remove List Item And Cell Contents, Then Shift Up
how to code the following?

I need to remove an item from a list, find its match in column "A", remove the matching cell and then shift the remainder of the column up to remove the empty cell.

If this helps, there are 5 different sheet names, and I'm working with range A4:Range("A" & Rows.Count).End(xlUp).Row for the items in the list.

To make it as simple as I can:
COLUMN A
one
two
three
four

user clicked on two in the list, so column A now looks like, with no gap between one and three:

COLUMN A
one
three
four

One sheet actually needs it to do the same removal to identical rows in column B as well (i.e. remove A10 and B10, or A45 and B45, etc.)

View Replies!   View Related
Values Which Are Equal Not Treated Equal
I'm having trouble with a small vba macro. At the end of the macro I test to see if two variables are equal and then print out true or false. However, for some reason even though the variables are equal vba is not treating them that way. I have put the values that represent the variables on a spreadsheet and used the if(x1=x2) formula and it says it is true, also, when I debug the macro and watch the values when it comes to test the logical expression the numbers are the same. I don't understand why vba does not say that the two variables are equal. I have attached a screenshot of the breakpoint where I double check the values are equal.

View Replies!   View Related
Macro That If I Hold Control + Shift + A I Will Get A Pop Up Saying "Your Amount Is (value Of The Cell)
I have an excel sheet and I want a Msg pop up to show a certain amount from a specific cell. So if the cell shows $100. I want to a do a macro that if I hold control + shift + a I will get a pop up saying "Your amount is (value of the cell).

I can only get it to work if I want the vale from an active cell.

MsgBox "The value in the active cell is " & ActiveCell.Value & "."


View Replies!   View Related
Delete Rows Where Cell Doesn't Equal Adjacent Cell
I have a sheet in Excel 2004 for Mac that is a patient list with over 2000 patients. I need a rule that will compare values (patient IDs and responsible party ID) in two adjacent cells, if the values are different, to delete the entire row. The goal is to get rid of patients (rows) that are not the responsible party (responsible party ID).

View Replies!   View Related
Delete Cell And Shift Cell Up
I have data in cells A1:A4. I want if cell A2 is deleted using the button delete, the cell will automatically shift up. Can a macro do this?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved