VBA to clear cells below header containing word

[higguns]

hello,

New to excel VBA and could use some help.

I am looking for a VBA to be able to use on a few different spreadsheets which will be automatically created from an android app that exports to a .csv file. After the csv file is created by the app, I need to be able to: A) clear all cells in a column under headers containing a specific word and B) save the file as a copy of the original file, with " - blank" added to the end of the file name.

All help appreciated!

Thanks

 

[The_Macrotect]

Hi there,

When you say "containing a specific word", do you mean in the headers or the cells below? Can you give an example?

 

[higguns]

the header contains the word, then all the cells below the header are cleared.

example:
the macro/vba would search for the word "time" in the active sheet and finds it in the header of column D. All cells in column D are then cleared of any content. if column E also contains the word "time" all cells in column E should also be cleared of any content.

 

[jolivanes]

Try this on a copy of your workbook.

Sub Maybe()
Dim lc As Long, i As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lc
        If InStr(Cells(1, i), "time") > 0 Then Range(Cells(1, i), Cells(1, i).End(xlDown)).Offset(1).ClearContents
    Next i
End Sub

 

[Rick Rothstein]

Try this on a copy of your workbook.

Sub Maybe()
Dim lc As Long, i As Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 1 To lc
        If InStr(Cells(1, i), "time") > 0 Then Range(Cells(1, i), Cells(1, i).End(xlDown)).Offset(1).ClearContents
    Next i
End Sub

You can also do it with a single line of code...

[table="width: 500"]
[tr]
	[td]Sub MaybeToo()
  Intersect(ActiveSheet.UsedRange.Offset(1), Rows(1).Find("time", , xlValues, xlPart, , , False, , False).EntireColumn).ClearContents
End Sub[/td]
[/tr]
[/table]

EDIT NOTE: Alternately, you can also do it with this slightly shorter one-liner as well...

[table="width: 500"]
[tr]
	[td]Sub MaybeAlso()
  Rows(1).Find("time", , xlValues, xlPart, , , False, , False).Offset(1).Resize(Rows.Count - 1).ClearContents
End Sub[/td]
[/tr]
[/table]

 

[higguns]

You can also do it with a single line of code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MaybeToo()
  Intersect(ActiveSheet.UsedRange.Offset(1), Rows(1).Find("time", , xlValues, xlPart, , , False, , False).EntireColumn).ClearContents
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

EDIT NOTE: Alternately, you can also do it with this slightly shorter one-liner as well...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MaybeAlso()
  Rows(1).Find("time", , xlValues, xlPart, , , False, , False).Offset(1).Resize(Rows.Count - 1).ClearContents
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

The first one deletes only the next row, the second and third codes provided clear the whole row which is what I was looking to accomplish however; it should keep looking in the rest of the sheet for more columns which have the same word "time" and clear those ones too. Also since there are many columns with other named headers I need to clear, I'd like to add additional word searches/cycles to clear those column as well.

Once the above is completed, is there a way to automatically save-as the file with the original file name with the suffix "-blank" to the end in the same folder as the original file?

 

[higguns]

I added another row to the 3rd code for the next word to search and clear cells below and it works... it looks like this:

Sub MaybeAlso()
  Rows(1).Find("time", , xlValues, xlPart, , , False, , False).Offset(1).Resize(Rows.Count - 1).ClearContents
  Rows(1).Find("certified", , xlValues, xlPart, , , False, , False).Offset(1).Resize(Rows.Count - 1).ClearContents
End Sub

Just wondering if there's a way to shorten that into one line with all the words I want it to search for?
And it would ideally keep searching for the same word in other headers for this sheet before checking the next word.
Also since there are multiple sheets with different headers, if the header doesn't exist can it skip to the next without causing an error?
Lastly it should be able to save each file automatically from the same macro (or separate if needed) adding a suffix of "-blank" to the end.

Thanks in advance for everyone's help

 

[jolivanes]

If you want the header deleted also, change this

If InStr(Cells(1, i), "time") > 0 Then Range(Cells(1, i), Cells(1, i).End(xlDown)).Offset(1).ClearContents

to this

If InStr(Cells(1, i), "time") > 0 Then Range(Cells(1, i), Cells(1, i).End(xlDown)).ClearContents

What are these words that you want to look for in these multiple sheets?
Are there any sheets that will be excluded or do all sheets need to be checked?

 

[higguns]

sorry I meant in multiple workbooks. the other workbooks have different headers and I want to be able to add/change the word search and delete below header with the specified text as needed. The headers should remain in tact.

I am using an android app for monthly/annual inspections that exports the data to a .CSV file. I need an easy way to clear all data in columns below specific headers, save-as with the suffix -blank added to the filename so that I can take the base inspection data and import it back into the app to do the next monthly/annual inspection so that I don't have to record all data that stays the same again and again.

 

[jolivanes]

Workbooks don't have headers. Worksheets do.
Do you want to loop through all workbooks in a folder?
If so, is the workbook with the code in it also in that folder?
Do you need to check all sheets in every workbook?