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
Solved

VBA to delete range of cells in row NOT entire row

Posted on 2016-10-10
11
48 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
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

861 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