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

Clear Contents From Specific Row

Posted on 2014-01-03
4
371 Views
Last Modified: 2014-01-04
I found this code on the web here - http://www.vbaexpress.com/forum/archive/index.php/t-31071.html - and assigned it to a command button. It works fine for the entire row but I want to clear contents from left of column E. In other words, clear contents from A to E only.


Dim rStart As Range, rLast As Range, rConstants As Range

 If Not TypeOf Selection Is Range Then Exit Sub

 Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
 Set rLast = Selection.Rows(1).EntireRow.Cells(1, Selection.Parent.Columns.Count).End(xlToLeft)
 Set rConstants = Nothing
 On Error Resume Next
 Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
 On Error GoTo 0

 If Not rConstants Is Nothing Then
 Call rConstants.ClearContents
 End If

Thanks!
0
Comment
Question by:tracyms
  • 2
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39755465
Find and change:

Set rLast = Selection.Rows(1).EntireRow.Cells(1, 5)
0
 
LVL 1

Author Comment

by:tracyms
ID: 39755493
Works great, thanks! I will award you the points. I'm curious if this will this work on specific cells? For example, if I wanted to clear contents in A-E but skip C? I have another spreadsheet that I may want to try this on.
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 39756795
Sub test()
Dim rStart As Range, rLast As Range, rConstants As Range
Dim rStart2 As Range, rLast2 As Range, rConstants2 As Range

 If Not TypeOf Selection Is Range Then Exit Sub

 Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
 Set rLast = Selection.Rows(1).EntireRow.Cells(1, 2)
 Set rStart2 = Selection.Rows(1).EntireRow.Cells(1, 4)
 Set rLast2 = Selection.Rows(1).EntireRow.Cells(1, 5)
 
 Set rConstants = Nothing
 Set rConstants2 = Nothing
 On Error Resume Next
 Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
 Set rConstants2 = Range(rStart2, rLast2).SpecialCells(xlCellTypeConstants)
 On Error GoTo 0

 If Not rConstants Is Nothing Then
 Call rConstants.ClearContents
 End If
 If Not rConstants2 Is Nothing Then
 Call rConstants2.ClearContents
 End If
 
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:tracyms
ID: 39756907
Thanks so much! Experts Exchange is awesome! :-)
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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

792 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