PDA

View Full Version : Delete unwanted Data



iwelcomesu
05-25-2011, 05:47 AM
Hi All,

I have a worksheet that contains around 60 to 70 sheets, in each sheet column A contains some data after that a blank cell and a symbol like this " " how to delete in all sheet a blank cell and the symbol at once.

Thanks for your help.

Regards,
Hari

pjotter
05-25-2011, 06:11 AM
Hi All,

I have a worksheet that contains around 60 to 70 sheets, in each sheet column A contains some data after that a blank cell and a symbol like this " " how to delete in all sheet a blank cell and the symbol at once.

Thanks for your help.

Regards,
Hari

So Column B is blank and Column C contains:" "? or what do you mean? Please be more specific, else I do not see what way to help you...

To loop through the sheets you should use the following though:


Sub LoopSheets()
Dim dataSheet As Worksheet
For Each dataSheet In ThisWorkbook.Worksheets

<Enter code for deleting whatever on each worksheet>

Next dataSheet

End Sub

iwelcomesu
05-25-2011, 06:46 AM
Hi,

In all my worksheets data contians from A to D cloumn but, I want to delete specific data from cloumn A in each sheet. refer to attached screenshot for yor reference.

Regards,
Hari

Benzadeus
05-25-2011, 07:13 AM
Sub RemoveUnwantedCells()
Dim ws As Worksheet

For Each ws In Sheets
ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(-1).Resize(2).ClearContents
Next ws

End Sub

pjotter
05-25-2011, 07:17 AM
So you basically want the "End of report" that is in cel B14 in this instance, to moe to Cell A14 and have Cells A14 and A15 deleted... (On this worksheet, while the "End of Report" might be on another row in another sheet, right?)

Well for that you need to do the following.
Sub LoopSheets()
Dim ws As Worksheet
For Each ws In Sheets
ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(-1).Resize(2).Delete Shift:=xlShiftToLeft
Next ws
End Sub

iwelcomesu
05-25-2011, 07:27 AM
Hi,

basically as per the attached screenshot "end of report" contians in cloumn B and symbol is avilable in after this cell in cloumn A, both are in same sheet, I need to delete both, this code is not working it is showing as "next" error.

Thanks for your help.

Regards,
Hari

shrivallabha
05-25-2011, 08:36 AM
Maybe this 'edited code' should do it for you:
Sub LoopSheets()
Dim ws As Worksheet
For Each ws In Sheets
ws.Cells(ws.Rows.Count, "B").End(xlUp).Offset(, -1).Resize(2).Delete Shift:=xlShiftUp
Next ws
End Sub

iwelcomesu
05-25-2011, 11:46 PM
Hi shrivallabha,

Thanks for your code, my actual reqirement is find the first blank cell in the Column A in all the sheets and delete that row and after rows, ex. find first blank row and delete that row along with below two rows.

Can you please provide code for above requirement.

Thanks for your help.

Regards,
Hari

shrivallabha
05-26-2011, 07:01 AM
Hi Hari,

Supposing that the blank cell occurs on the same row as words End of Report come this code should work:
Sub LoopSheets()
Dim ws As Worksheet
Dim r As Range
For Each ws In Sheets
Set r = ws.UsedRange.Find(What:="End of Report", LookAt:=xlPart)
If Not r Is Nothing Then
ws.Rows(r.Row + 1).Delete
ws.Rows(r.Row).Delete
End If
Next ws
End Sub

iwelcomesu
05-27-2011, 12:08 AM
Hi,

Thanks for your help, I need your help regarding below code

Sub macro1()
Dim aryData As Variant
Dim sh As Worksheet
Dim i As Long
Dim r As Range
With ActiveWorkbook

ReDim aryData(1 To .Worksheets.Count, 1 To 2)
For Each sh In .Worksheets
With sh
.Range("A119:A124").EntireRow.Delete
.Range("A60:A65").EntireRow.Delete
.Range("A1:A6").EntireRow.Delete
End With
Next sh


For Each sh In Sheets
Set r = sh.UsedRange.Find(What:="End of Report", LookAt:=xlPart)
If Not r Is Nothing Then
sh.Rows(r.Row + 1).Delete
sh.Rows(r.Row).Delete
End If
Next sh


For Each sh In .Worksheets
With sh
i = i + 1
aryData(i, 1) = sh.Name
aryData(i, 2) = .UsedRange.Rows.Count
End With
Next sh



.Worksheets.Add after:=.Worksheets(.Worksheets.Count)
i = 0
With ActiveSheet

For i = 1 To .Parent.Worksheets.Count

.Cells(i, "A").Value2 = aryData(i, 1)
.Cells(i, "B").Value2 = aryData(i, 2)
Next i
End With
End With

End Sub


this code helps to delete unwanted data in all the sheets and count the lines in each sheet and update the count in new sheet, while running above it is showing wrong output, if the column A is blank output as showing count 1, can you look at this code and help to find where the problem is.


Regards,
Hari

shrivallabha
05-27-2011, 10:04 AM
Hari,

The count will never be zero and it will be at least 1. Therefore it needs a small checking loop.


For Each sh In .Worksheets
With sh
i = i + 1
aryData(i, 1) = sh.Name
If .UsedRange.Rows.Count = 1 Then
aryData(i, 2) = 0
Else
aryData(i, 2) = .UsedRange.Rows.Count
End if
End With
Next sh


The code you have posted seems to be combo-code that you have gathered from different requests on the forum. It would have looked better, had you posted a single sample workbook with all your requirements.