Solved

VBA to delete range of cells in row NOT entire row

Posted on 2016-10-10
11
47 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

809 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