Solved

VBA to delete range of cells in row NOT entire row

Posted on 2016-10-10
11
69 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

630 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