+ Reply to Thread
Results 1 to 4 of 4

Macro to delete cell and its adjacent cell

Hybrid View

tghiotto Macro to delete cell and its... 01-08-2014, 10:09 AM
Kaper Re: Macro to delete cell and... 01-08-2014, 10:27 AM
stnkynts Re: Macro to delete cell and... 01-08-2014, 11:21 AM
tghiotto Re: Macro to delete cell and... 01-08-2014, 12:59 PM
  1. #1
    Registered User
    Join Date
    01-08-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Macro to delete cell and its adjacent cell

    Hello and thank you in advance for any help i may receive. My question is How do i delete a a cell in Column B and also clear the adjacent cell in Column A. Column B is a number which remains constant to a certain point and Column A has a corresponding names which varies. I also need the cells below to move up as they are deleted in both columns but stop when Column B reaches a new number. The following is what i have so far and it works great for column B. I cant figure out how to get Column A to go with it though.

    
    Sub Delete930()
    Dim rng As Range, i As Integer
        Set rng = Range("B1:B500")
        For i = rng.Rows.Count To 1 Step -1
          If rng.Cells(i).Value = "930" Then rng.Cells(i).Delete Shift:=xlUp
          
            
         
        Next
    End Sub

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,861

    Re: Macro to delete cell and its adjacent cell

    Try
    If rng.Cells(i).Value = "930" Then rng.Cells(i).offset(0,-1).resize(1,2).Delete Shift:=xlUp

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Macro to delete cell and its adjacent cell

    Sub Delete930()
    Dim i As Integer
    
        For i = 500 To 1 Step -1
          If Range("B" & i).Value = "930" Then
            Range("A" & i).Resize(1, 2).Delete Shift:=xlUp
          End If
        Next i
        
    End Sub

  4. #4
    Registered User
    Join Date
    01-08-2014
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Macro to delete cell and its adjacent cell

    Kaper that worked great. However another problem arose. I enclosing a sample worksheet for anyone that might be able to help. I am new at this so I am probably going the long way about things. What I am trying to accomplish is.. I have a list of employees and start times. The number of each may vary. I want to be able to match the name and start time, pick them from the list and put them in the appropriate location on other worksheets. I have 90% of this figured out already i believe. I am using IF statements in each cells to redirect the info to diff pages.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to delete adjacent cells values based on one cell?
    By meprad in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2013, 09:07 AM
  2. [SOLVED] Identify Cells with value of 0 and delete 0 cell and adjacent cell
    By brgr4u in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2013, 09:04 AM
  3. Delete adjacent cell
    By kryt0n in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-12-2011, 10:39 AM
  4. Delete cell contents with input to adjacent cell
    By Ashley Frank in forum Excel General
    Replies: 1
    Last Post: 10-05-2005, 12:05 PM
  5. [SOLVED] can I delete entire row if adjacent cell formats differ?
    By ksukid in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-19-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1