Using VBA And If Statements To Delete / Shift Up And Insert Cells

Sep 7, 2012

I am trying to create a macro that will add and delete individual cells depending on an IF statement. The code that I created deletes/adds cells regardless of the IF statement.

If Sheet1.Range("F3").Value = Sheet1.Range("G2").Value Then
Range("G2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End

ElseIf Sheet1.Range("F3").Value = Sheet1.Range("G4").Value Then
Range("G3").Delete Shift:=xlUp
End
End If

Also, what is the easiest way to have this code apply to all rows within column F, while adjusting the corresponding cells referenced in column G? The last row within the spreadsheet changes.

View 3 Replies


ADVERTISEMENT

Insert A Value And Shift Cells Value Down

Feb 6, 2009

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 9 Replies View Related

VBA Insert Copied Cells Shift Down

Sep 25, 2013

I'm using a macro to copy cells (2 columns and between 1 and 5 rows [19 - 23 on s/s]) from one workbook to another but if I use

Rows("2:2").Select
Selection.Insert Shift:=xlDown

then every cell in the inserted rows are populated.

this is what I currently have

Windows("agent master.xlsm").Activate
Range("B19:C23").Select
Selection.Copy
Windows("distribution master.xlsm").Activate
Sheets("L38").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

but that doesnt work either. It just gives me an empty cell in A2.

View 1 Replies View Related

Insert-shift Cells Down Macro

Jan 29, 2009

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 9 Replies View Related

Delete Cells And Shift Up

Nov 7, 2009

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 5 Replies View Related

Delete Cells, Shift Everything Else Below It Up

Feb 6, 2007

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 9 Replies View Related

Insert Cut Cells - Paste And Shift Cells Down

Aug 18, 2014

Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.

View 1 Replies View Related

Delete Range Of Cells And Shift Up

Jan 30, 2013

I am trying to delete a group of four cells and have the remaining block shift up. My code is below and crashes on the red. What is the right code to get this to work?

Code:

Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = Range("G1").Value Then
Range(ActiveCell:ActiveCell.Resize(0, 3)).Delete Shift:=xlUp
Exit Do
End If
Loop

View 1 Replies View Related

Delete Row Macro (shift Cells Up Then)

Jul 1, 2007

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 9 Replies View Related

Delete Cells Shift Left

Mar 15, 2007

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 9 Replies View Related

Delete Empty Cells, And Shift Left

Oct 7, 2008

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 5 Replies View Related

Delete Empty Cells And Shift Remaining Left

Oct 7, 2008

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 2 Replies View Related

Delete And Shift Cells Based On Cell Values

May 23, 2007

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 8 Replies View Related

Insert Column & Shift Left

Sep 14, 2007

I wanna shift a particular column (lets say column A) to the left, so I do the following:

hojaFAC.Columns("A:A").Select
Selection.Insert Shift:=xlToLeft

hojaFAC is a worksheet

BUT I get the next error when I execute my macro :
RUNTIME ERROR 1004
Select method of range class failed

Why do I get runtime error nš 1004 all the time also for other reasons. I think itīs the only runtime error I ever jumped into. But If I ever get a runtime error It's allways number 1004.

View 4 Replies View Related

Excel And VBA - Insert Group Of Cells Or Delete Them?

Apr 12, 2013

I am working on a contact information form for given relationships, and I'm trying to figure out the best way to design a button that copies the "form" (not an actual VBA form but a grouping of formated cells for information collection) and inserts it into the document. I would also like a button that deletes unused and empty forms so that we only print forms that are filled in.

I tried recording a macro to do it, but it's messy and creates issues with hidden rows and pagination.

View 4 Replies View Related

Delete Row And Shift It Up If Value In Column

May 15, 2014

How would write a macro that would delete a row and shift up if they have a value in Column A?

View 6 Replies View Related

Delete Cell And Shift Left?

May 28, 2014

I want vba code to do the changes

Search for "CAP_ACTV_LN_SEQ" keyword in column A of Sheet1 and when found go two rows down and do the following

Delete cell D128 and E128 ( if more than 1 rows then do the same for all rows till "selected" keyword detected below it eg C131 in this case ) and Shift cells in the right to two column left as shown in figure below -

Before
2.jpg

After
3.jpg

Sheet attached

Data.xlsm

This can happen by click of button in the sheet

View 3 Replies View Related

Excel 2010 :: Unable To Right Click For Format Cells - Insert Or Delete

Sep 27, 2011

Just recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.

I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.

Running Office standard 2010, Windows 7 pro x64

View 8 Replies View Related

When Protect The Sheet - Delete And Shift Up Disable

Aug 8, 2013

I have a sheet,i want the users unable to select any cells,but a column is free to select an change and enter the inputs. I want to delete and shift up zero cells on that free column,but this property is disable when i protect the sheet.

How I protect other cells from selection and change,and enable delete and shift up for the column that not protected.

For example: I have the attachment file,every column is protected except column "H". I want a macro to when I clear content of any cell in this column,every cells shift up and every non zero cell put from row 1 to... and after them zero cells put.

View 2 Replies View Related

Compare Dates In Rows - Delete Then Shift Up

Mar 14, 2012

I am fitting 2 sets of data to match eachother. I want to have as much data as possible but only for the same dates of each dataset.

I have 4 coloumns.
Dataset 1: A is date B is data
Dataset 2: C is date D is data

I want to compare A vs B one row at a time. If they are not similar it should delete and shift up the values in A+B or C+D according to the lowest value (date) of A vs B.

Remember that if the macro just deleted and shifted up it should compare the same (undeleted) value against the new value that just shifted up in the other coloumn.

View 2 Replies View Related

VBA Code To Convert Excel To Text File Of DML Statements (Insert) Using Macro

Jun 11, 2014

I need a VBA code to create a Text file which should be in the form of DML (my scenario is to get Insert Statements for the Data available in the Excel) from Excel Sheet. And this has to be done by assigning a macro to command button where by clicking on it the DML text file should be generated. Even the syntax for the scenario will do.

View 1 Replies View Related

Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)

Dec 1, 2009

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 3 Replies View Related

Calculate Pay For Shift Work With Different Rates Based On Shift Hours

Apr 11, 2008

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 8 Replies View Related

VBA To Shift Cells To The Left

Apr 30, 2009

how can i get this

Sheet1

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

View 9 Replies View Related

Search Cell If Blank Delete Cell Shift Values Up

Oct 22, 2009

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 2 Replies View Related

Shift Cells Down Depends On Condition Of Others

Oct 20, 2009

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 5 Replies View Related

Shift Cells Up A Row To Align With Column Using VBA

Jan 13, 2012

I need to shift all the cells in columns B, C, D, E & F up a row to align with the names in column A.

I have code to delete the empty rows.

Start:

Bank Holiday 1 on a Week End

ABCDEF1YASMIN AKHTAR (YA1) 2 59038.753MARINA BELAIDI (MB2) 4 12325.55MARGARET CANT (MC6) 6 33002.25

Result Required:

Bank Holiday 1 on a Week End

ABCDEF1YASMIN AKHTAR (YA1)59038.752 3MARINA BELAIDI (MB2)12325.54 5MARGARET CANT (MC6)33002.25

View 3 Replies View Related

Shift Cells Up If Cell Equal 0

Dec 27, 2008

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 9 Replies View Related

Make Cells Shift Down Automatically

Jun 18, 2009

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 9 Replies View Related

'shift Cells Right' Depending On Value Of Cell

Jan 4, 2010

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 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved