Solved

VBA to delete range of cells in row NOT entire row

Posted on 2016-10-10
11
57 Views
Last Modified: 2016-10-19
I am using a Macro which deletes the entire row but I need to change this to only delete V, W, X and Y within the row. Can this code be changed to do this?

 Cells(lngRow - 1, 4).EntireRow.Delete

 Thanks
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41837232
Here you go:
Sub Demo()

    Dim lngRow As Long
    Dim lngLastRow As Long

    lngLastRow = Worksheets("Original").Columns("B").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

    With Worksheets("Original").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("B2:E" & lngLastRow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    For lngRow = lngLastRow To 2 Step -1
        If Left(Cells(lngRow, 2), 1) = 7 Then
            If Cells(lngRow, 2).Value = Cells(lngRow - 1, 2).Value And Cells(lngRow, 5).Value = Cells(lngRow - 1, 5) Then
                Cells(lngRow, 4).Value = Cells(lngRow, 4).Value + Cells(lngRow - 1, 4).Value
                Cells(lngRow - 1, 22).Value = ""
                Cells(lngRow - 1, 23).Value = ""
                Cells(lngRow - 1, 24).Value = ""
                Cells(lngRow - 1, 25).Value = ""
            End If
        End If
    Next

End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41837600
Hi Macroshadow

It's not working what it is doing is putting a space between them like this

7001      12/08/2016      252.282      JK5 JWK MFS  STMT 11-08-16
                  
7001      12/08/2016      4.671      JK5 JWK MFS  STMT 11-08-16

but the original gave me one line like this

7001      12/08/2016      256.953      JK5 JWK MFS  STMT 11-08-16

Regards
Jagwarman
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41837806
Can you upload a sample file?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 27

Expert Comment

by:MacroShadow
ID: 41837811
These rows replace  Cells(lngRow - 1, 4).EntireRow.Delete:
Cells(lngRow - 1, 22).Value = ""
Cells(lngRow - 1, 23).Value = ""
Cells(lngRow - 1, 24).Value = ""
Cells(lngRow - 1, 25).Value = ""

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 41837936
File attached. Originally I was using B-E but now need to use V-Y as per the attached file

Thanks
Find-and-Move-JK.xlsm
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41838001
You asked to delete cells V, W, X and Y, that is exactly what the code does.
Tested on your sample file.
0
 

Author Comment

by:Jagwarman
ID: 41838008
Hi MacroShadow.

In my message to you I referred you back to the Macro solution you provided me with and said :

last line of code is

23:                Cells(lngRow - 1, 4).EntireRow.Delete

unfortunately I have had to make changes to our file and instead of deleting the whole row only want to delete V,W,X and Y

So as the last line of your code deletes the whole row I asked can you instead change it so that it only deletes V,W,X and Y but still it needs to perform the other part of the macro which it does perfectly  prior to deleting the whole row.

I am sorry if I misled
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41838013
I must be missing something...
The only thing to change is replace
Cells(lngRow - 1, 4).EntireRow.Delete

Open in new window

with
Cells(lngRow - 1, 22).Value = ""
Cells(lngRow - 1, 23).Value = ""
Cells(lngRow - 1, 24).Value = ""
Cells(lngRow - 1, 25).Value = ""

Open in new window

Keep the rest of the code, it will do whatever it is supposed to do.
0
 

Author Comment

by:Jagwarman
ID: 41839138
not working for me, very odd will look again tomorrow.
0
 

Author Comment

by:Jagwarman
ID: 41841431
Hi MacroShadow,

I have checked this at home and at work and cannot get it to work for me. I am off now for a weeks vacation but will do some more checking when I get back.
0
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 41841783
I'll say it again, to clear cells in columns V, W, X and Y, the code I provided does the job.
Cells(lngRow - 1, 22).Value = ""
Cells(lngRow - 1, 23).Value = ""
Cells(lngRow - 1, 24).Value = ""
Cells(lngRow - 1, 25).Value = ""

Open in new window

Alternatively, you can use the Range object's ClearContents method:
Range("V" & lngRow - 1 &":Y" & lngRow - 1).ClearContents

If any other code is not functioning properly, ask a new question.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question