Free Microsoft Excel 2013 Quick Reference

Clear Contents of Rows with 1 empty cell

Hi,

I am afraid im quite new to excel and have just started writing macros make life easier. I will try and explain my problem as best as I can:

I want to select B2:G11
Sort the data by column E
Then clear the contents of the cells in the rows where there is no numerical value in column E.

I have written a macro that enables me to select and sort the data but I cannot work out how to do the rest.

Some very clear instructions would be greatly appreciated.

Thank you.


Post your answer or comment

comments powered by Disqus
Hi

I am using this macro which deletes the entire row if cell contents of columns M,N,O are empty.

Code:
Dim cell As Range, rng As Range, i As Long, LastRow As Range
  
LastRow = ActiveSheet.Range("K1511").End(xlUp).Row
Set rng = ActiveSheet.Range("M11:M" & LastRow)

For i = rng.Count To 1 Step -1
If LCase(rng(i).Value) = "" _
And LCase(rng(i).Offset(0, 1).Value) = "" _
And LCase(rng(i).Offset(0, 2).Value) = "" _
Then rng(i).EntireRow.Delete
Next i
However, i need to alter this to clear the cell contents of range E:W for a particular row that has cells in columns M,N,O empty.

I can't figure out how to do that.

any help please ?
thank you
andy

hello,

i have a workbook which shows data in rows, across columns 'a' thru 'k'
in column 'L' of every row i would like to add control button. clicking the button clears the contents of cells 'a' thru 'k'
i don't want to clear the entire row - just a - k
clicking on the control button in one row will delete the contents of that row, columns a- j

ie click control at L5 - clears a5 - k5 etc

I have tried the following...
1.

Range("A4:I4").Select
Range("I4").Activate
Selection.ClearContents
End Sub
when copied to controls in other rows, this just clears contents of row 1.

2. I guess I need to do something similar but with activecell..tried this without success...

ActiveCell.Offset(0, -9).Range("a4:j4").Select
Selection.ClearContents

however i get a run-time error 1004.
Any help would be greatly appreciated!
thanks

I am trying to delete all rows with non-empty cells (contains text) from a specific column in the header, while avoiding loops if possible.

Sub DeletenonblankRows()
Columns(Rows("1").Find("Sys_Sxn").Column).Select
ActiveSheet.Columns(Selection).SpecialCells(xlTextValues).EntireRow.Delete
End Sub

What is wrong?

Thanks.

Hi all.

I have a spreadsheet with related information in each row. What I need to
do is email a list of items that do not have a completed date entered if it
is past the current date.

I have a function that send an email. I can find the empty cells but what
is the best way to compile the list of rows that i need and then add then to
an email? Can I use an array or a set of arrays? Can I send an email with
that much information using the following:

With OutMail
.To = strTO
.Subject = strSubject
.HTMLBody = strBody
.Send
End With

Hi.

I've seen macros on clearing contents based on conditions in other cells, but cannot piece together the code given multiple ranges.

Here's my request: Beginning at row 5 in my spreadsheet, I need a macro to clear the contents of rows in the column ranges AJI:AOJ and AOO:AOS if the cells in column AOT are blank. This macro should perform this function down 500 rows.

Please advise if a sample spreadsheet would be helpful.

Thanks so much for the help!

I am working with Excel 2007. I would like to write a macro that hides rows
based on an empty cell in that row, the same cell in each row.
It would have to be able to look so far down a spreadsheet hiding rows with
that empty cell, C??, then continue on beyond that with cells containing
data, then hiding rows after that with the empty C cell.
I would be most grateful if anyone can help.
--
Mac Macdonald

Good Morning -

I would like to clear the contents of Some Rows in my spreadsheet.

I would like to do it based on the resulting Empty Cells found by Control G - Special - Blanks within my highlighted range in column C.

So, I am looking to Clear the Contents of Rows that contain the then highlighed blank cells in Column C.

Can you help

Much Appreciated

Hi everybody,

I am a financial administrator & every month I have down load 4 bank accts as CSV, import

into xl & code (CACode) the amounts for our accountant.

I have VBA that formats, adds headings & formula etc but I have a problem/s.

In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA).

Now I am trying write a macro to clear the contents of the cell in I col
if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/then
If Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct.

I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals

Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.


	VB:
	
 sort_And_delete_Sumif_amounts() 
     '
     ' sort_And_delete_Sumif_amounts Macro
     ' Macro recorded 6/03/2007 by Lionel Hansen
     'This deletes sumif formula in I Col if
     'Last cell CAcode = 2nd last cell CAcode in H Col
     
     '
    Dim r As Range 
     'Select range to sort
    Range("A2:I98").Select 
     'Sort CAcode in H col ascendingly
    Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
     'select range for comparisomn of CAcode
    Set r = Range("H3", Selection.End(xlDown)) 
     'Select last cell in CAcode range to start If/Then
    Range("h3").Selection.End(xlDown).Select.Range ("A1") 
    For Each Cell In r 
         'Compare CAcode in H Col
        If Range("A1").Value = ActiveCell.Offset(-1, 0).Value Then 
            ActiveCell.Offset(0, -1).Range("A1").ClearContents 
            ActiveCell.Offset(-1, 1).Range("A1").Select 
        Else: ActiveCell.Offset(-1, 0).Range ("A1") 
        End If 
    Next Cell 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance

I want to clear the contents of certain cells if other data on the
same line meets my criteria. For instance, if cell V8=1, I want to
clear the contents (not delete) of cell M8.

I have some code that gets close, but doesn't quite get me what I
want. I have tried to tinker with it, but to no avail.

------------------------------------------------------------------

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
Firstrow = ActiveSheet.UsedRange.Cells(8).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "V").Value) Then
ElseIf .Cells(Lrow, "V").Value = "1"
Then .Rows(Lrow).Delete

'''''''''''''''''' .Rows(Lrow).Delete should be replaced
with something that
tells it to clear contents of the cell in column
M that corresponds
to a value of 1 in column V

End If
Next
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

------------------------------------------------------------------

As always, any help on this issue would be much appreciated. Thanks!

Kevin

I'm using a macro to copy the results of a formula and paste the values only on another sheet. The result includes lots of "blank" rows. I have another macro to get rid of the empty rows and move the information up.

It's not working because the "blank" rows aren't empty. Even though I paste values only, experimentation shows that the cells that appear blank return a false to the ISBLANK test with a length of 0.

So now I think I need a macro to run after the pastespecial command to look for cells within a range with a length of zero and delete the contents of those cells, but leave alone anything with a length of >0.

I am brand new to the idea of using VBA, but I have successfully cobbled together some stuff and can usually modify things to work.

It seems I need to maybe use some sort of IF statement along with a LEN and ClearContents. I don't want to delete the blank cells, just make them truly empty so that all of my actual data stays where it should, and my delete empty rows macro works correctly.

I did a search and see some info on clearing contents of columns or rows, or clearing contents based on the content of other columns or rows, but I'm unsure of how to tell it to search each cell within a range and clear the contents of 0-length cells to make them truly empty.

Thanks muchly for your advice!

Jenny

read cell content and use with Hyperlinks.Add Cell etc. etc.

I use the code below to read Cell-Content in Column:A and use that to generate a hyperlink with the content off the/a cell.
The auto-hyperlink works great BUT there seems to be a small flaw in the vba code.
If I delete the content of a cell (which allready has been auto-hyperlinked with the vba code) then it replaces the just emptied content of the cell with the url (text) => http://www.imdb.com/find?s=all&q= instead of deleting/clear the cell content.

It seems to me that this line is the problem, at least in my opinion:
If Cell.Hyperlinks.Count = 0 Then... etc.
When I look at it, it seems obvious that when I delete the content of a cell (with delete) that the code will drop the http://www.imdb.com/find?s=all&q= line in the cell instead.
Because a completely emptied cell also is/becomes equal to 0, how could I overcome this?
Could anybody help me out please? I guess I need to define somehow that when I delete the content of a cell or when cell content is completely empty it should be ignored by the auto-hyperlink code.

Used in this vba-code (in WorkSheet):
' Auto-Hyperlink on Column:A  &  Auto-SORT  !!! !!! !!! !!! ! '
Private Sub Worksheet_Change(ByVal Target As Range)

If CheckBox1.Value = True Then

' (START) Auto-HYPERLINK by DEFAULT !!! !!! !!! !!! !!! !!! ! '
    Dim Sh As Worksheet
    Dim rng As Range
    '   only look at single cell changes
    Dim Cell As Range
    Set Sh = Worksheets("DVD Lijssie")
    Set rng = Sh.Range("A4:A" & Sh.Cells(Sh.Rows.Count, 1).End(xlUp).Row)
    '   only look at that range
    For Each Cell In rng
        If Cell.Hyperlinks.Count = 0 Then
            Sh.Hyperlinks.Add Cell, "http://www.imdb.com/find?s=all&q=" & Cell.Value
            With Cell.Font
                .Name = "Arial Narrow"
                .Size = 8
            End With
        End If
    Next Cell
' (END) Auto-HYPERLINK by DEFAULT !!! !!! !!! !!! !!! !!! !! '

' (START) Auto-SORT on Check(Box1) !!! !!! !!! !!! !!! !!! ! '
        If Target.Count > 1 Then Exit Sub
        Set rng = rng.Resize(, 7)
        ' The Resize property takes 2 arguments, RowSize and ColumnSize.
        ' If an argument is omitted, the number remains the same.
        ' So: Set rng = rng.Resize(, 7) => expands the existing rng to 7 columns,
        ' retaining the existing number of rows.
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        rng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' (END) Auto-SORT on Check(Box1) !!! !!! !!! !!! !!! !!! !!! '

Else
CheckBox1.Value = False
' DO NOTHING AT ALL WHEN FALSE/UNCHECKED/DISABLED
End If

End Sub
With kind regards, Tim

I have a macro using the change event. If an "x" gets typed into a certain cell, then the contents of the cells to the left gets cleared and the contents below get moved up so there is not a blank row in the range. All works fine except when I clear the contents of the cell that contains the "x", the change event gets triggered again in a continuous loop and all the cells are moved up and cleared one by one. However if I don't include the cell with the "x", then the "x" remains which I don't want to have happen. Does this make sense to anyone out there? There must be some simple work around.

Thanks so much in advance!

Hi, i need a macro written to clear contents of cells with criteria.

I have data starting in row A3, across 62 columns, this may vary depending on which month we are in. For an example i have chosen the month of May which has 31 days.

In Col A3 onwards is a list of p’folios and in Col B3 onwards is values associated with each p’folio. Col A and B represent 01-May, Col C and D represent 02-May etc
I need a macro to clear cells which have portfolios starting with “ZI” and “FI”, I don’t want the rows deleted but want the rows to move up one cell when data is cleared, I need this for every column of data.

So let’s look at Col A for an example, we can see it has p’folios ZISZAE and FISAAE, therefore will clear cells A3 and B3 move A4 and B4 one cell up, and the same scenario for A5 and B6 clear the contents and move Cells A6 and B6 up. The same can be repeated for all other columns,

Col A Col B Col C
01-May-08 Amount 02-May-08 Amount
3 ZISZAE 12 AIMTES 76.98
4 OPTMOD 15.8 ZIZZAE 89
5 FISAAE 344.87 CAETRN 76

Can someone help me with a macro that will clear contents in a range based on a cell?

im trying to get it so in range E30:E49, going down the range, the first empty cell will cause that entire row from A:L to be selected and contents cleared.
i want it to stop @ row 50, as there is data that needs to be kept here.

is this do-able?

Hi all, I have a worksheet filled with students information (ID,name,
nationality.. etc.) and we are requested to give a print outs of
students with any information missing from his record, so my plan is
to create a macro that do the following:
- loop throgh every row until it reachs the end of the worksheet
- in each loop it will check if any of the cells is empty
- if one of the cells is empty it will copy this row to a new sheet
and then start at the beginning of the next row then continue the
loop
- if none of the cells is empty (no information is missing) then go
to
the beginning of the next row then continue the loop.

this is what I came up with:

Dim tmp As Integer
Do Until IsEmpty(ActiveCell)
For I = 1 To 11 ' 11 columns in each row that I have to check
Do While bolFlagged = True
If ActiveCell.Value = Empty Then
sheet2nextcell ' a function that copies the
current row to another sheet
bolFlagged = False ' I used this variable for
students not to dublicate
GoTo ExitLoop
End If
ActiveCell.Offset(0, 1).Select
Loop
ExitLoop:
Next I
Cells(ActiveWindow.RangeSelection.Row, 1).Select 'first cell
in current row
ActiveCell.Offset(1, 0).Select

as you may see this code isn't correct at all because I am no
programmer I can't figure out what I am doing wrong, so any input
would be appreciated.

Hi,

I'm looking for a macro that can go through all the contents of Column A (which could continue for thousands of rows) searching for any cell that will contain the word "dos*". Now the cell may not be simply "dos" it could be "double dos" or "other dos" and I want to make sure to keep all the ones that have that sequence of letters in it wherever it may be. All cells that do not contain this text should have their contents cleared and once that's completed all cells that do contain "dos" should be formatted to the top of the sheet, under the title that's already there. I have attached a spreadsheet with one sheet to show what the data will look like before and another sheet to show what it should look like after.

Thanks for this, you guys have already tought me so much just by going through various threads on here. I really appreciate the help!

I am trying to write a macro that will allow the deletion of rows based on duplicates found in column "B," however I would like the row with the most cells populated to remain e.g.

John > ABC Corp > 21/02/12 > CP > "No Data" > "No Data" > "No Data"
John > ABC Corp > 21/02/12 > CP > WON > Tom > 24/02/2012
"No Data"> ABC Corp > "No Data"> CP > WON > Tom >"No Data"

"No Data" = Blank

In this case I would like to remove all rows except row 2 as it has the most info. The data range I am working on goes from $A$3:$T and is added to constantly. I am trying to tweak the following code to get the result I want:


	VB:
	
 
    Const StartRow As Long = 3 
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row 
    LastCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _ 
    SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column 
    Application.ScreenUpdating = False 
    Cells(StartRow, LastCol + 1).Resize(LastRow - StartRow + 1).FormulaR1C1 = _ 
    "=SUMPRODUCT(MAX((RC1:RC" & LastCol & _ 
    """"")*COLUMN(RC1:RC" & LastCol & ")))" 
    Cells(StartRow, LastCol + 2).Resize(LastRow - StartRow + 1).FormulaR1C1 = _ 
    "=IF(RC" & LastCol + 1 & "=SUMPRODUCT(MAX(R2C" & _ 
    LastCol + 1 & ":R" & LastRow & "C" & LastCol + 1 & _ 
    "*(R2C2:R" & LastRow & "C2=RC2))),"""",""X"")" 
    Columns(LastCol + 2).Value = Columns(LastCol + 2).Value 
    On Error Resume Next 
    Columns(LastCol + 2).SpecialCells(xlConstants).EntireRow.Delete 
    On Error Goto 0 
    Columns(LastCol + 1).Resize(, 2).Clear 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However whilst it isn't coming up with any errors it is not removing dupes the way I would like it too. Thanks for any help in advance, this is driving me nuts!

I am a novice here and am merely trying to plug column numbers into pre-existing code. I have tried using the following code to clear the contents of a range if either cell in two columns is greater than 0:


	VB:
	
 newt() 
     
    Dim cell As Range, strTemp As String, c As Variant 
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
    For Each cell In Intersect(Range("WT5:WT4000"), ActiveSheet.UsedRange) 
        strTemp = cell.Value 
        For Each c In Array("XZ") 
            strTemp = strTemp & Range(c & cell.Row).Value 
        Next c 
        If Trim(strTemp) = ">0" Then 
            Range("SN" & cell.Row, "ADQ" & cell.Row).ClearContents 
        End If 
    Next cell 
    Application.Calculation = xlCalculationAutomatic 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So, the two columns whose cells need to be greater than 0 are WT and XZ. If either column contains values greater than 0, then the range SN:ADQ will be cleared. However, nothing happens when I try running this.

I'd appreciate any help. Thanks!
Glenn

I'm try to create a macro that clears contents of all unlocked cells. Can I just add a Selection.ClearContents into this code somewhere?


	VB:
	
 ActiveSheet 
    .Protect 
    .EnableSelection = xlUnlockedCells 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Searched throughout many excel solutions for this macro but nothing came out..
I basically need a macro that would clear the contents of all the rows (and all the cells within) containing in the first cell a specifc word ("Total"). But the thing is that there isn't just "Total" in the first cell of some rows but "Total Eur", "Total Gbp", "Total Huf", etc..
Any brilliant codes to enlighten me?

Many thanks,

Ed

Hello everyone,

I am trying to make a template sheet into which a can paste a table of data, with a constant number of columns but varying numbers of entries (rows). I want to count the number of rows with data, but would rather not set a range and use COUNTA or something similar. So is there a way to start at the top of the table and count down until an empty cell is reached and return the number of rows?

Also once I have this figure, say x, how would I reference this in subsequent formulae?
Eg find the maximum number in one column with =MAX(A1:A'(1+x)')

Thanks
tek

Hi All,

I'm looking at creating a macro for the below document.

1) If the word 'Equipment' appears in column D and '0' (zero) appears in colomn F i would like the whole row to be deleted.

2) If the word 'Labour' appears in column D and '0' (zero) appears in colomn F i would like the whole row to be deleted.

3) if there is no text in coloumn D i would like to clear the contents of a row (not delete it).

Can someone help me write a macro for this?
I want to save it in my personal workbook.

Thanks in advance!

Hi everyone,

I never did a macro with a variable and don't know VBA that well. I always record my macros.

I think this one could be an easy one. Can you help?

Column G has cells with values and cells that are empty.

I sort by column G (A to Z), pushes the rows with empty cells in this column to the bottom of list. I then need to cut all these rows with the empty cells in this column (G). Finally I would like the macro to create a new worksheet, name it "Norp", then paste these fields in the "Norp" workesheet.

Very grateful to anyone that help me. Thanks again.

I am formatting sheet2 based on information in sheet1. If cell b4 is empty, I want to move the contents of the first not-empty cell in Col B up to b4 along with the information in the adjacent columns. I want to repeat the process down the page so that I eliminate empty rows.

Columns B-F are either filled or empty based on this function: =IF('Sales by Date'!$B4="w","w","").

Thank you.


No luck finding an answer? You could always try Google.