Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA to delete range of cells in row NOT entire row

Posted on 2016-10-10
11
Medium Priority
?
92 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

660 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