Need Help in VBA !!

simplyeshu · Jul 25, 2011

I have a excel sheet.
I want to delete the top 41 rows; it consists of some logos and information.
I have the data from 42nd row, total columns 14
Each row is separated with one empty row
Some of the values of the row are suppressed if the value is same as upper row which I want to be displayed (please see the desired format in red)

At the end of the information, there are three empty rows followed by some information which should not be there.

Present format:

42) Myref Currcode payamt Bname Binfo Branch Rcode desc Ttype
43)
44) 1254 INR 55 ABC 12 US 3 Faulty device pay
45)
46) 1268 INR 90 XYZ 09 JPN 4 Display problem pay
47)
48) 4G No detection
49)
50) 5A Circuit problem
.
.
.
So on
157) last record
160) @Inc of all taxes Date: 01/01/2011

Desired format:

I want first 41records to be deleted
42) Myref Currcode payamt Bname Binfo Branch Rcode desc Ttype
43) I want empty rows to be deleted between rows
44) 1254 INR 55 ABC 12 US 3 Faulty device pay
45) I want empty rows to be deleted between rows
46) 1268 INR 90 XYZ 09 JPN 4 Display problem pay
47) I want empty rows to be deleted between rows
48) 1268 INR 90 XYZ 09 JPN 4G No detection Pay
49) I want empty rows to be deleted between rows
50)1268 INR 90 XYZ 09 JPN 5A Circuit problem Pay
.
.
.
So on
157) last record
I want last record to be deleted (in this case, it is 160<sup>th</sup> but it can differ based on data)
160) @Inc of all taxes Date: 01/01/2011

p45cal · Jul 25, 2011

What VBA have you got so far?

taurean · Jul 25, 2011

This should get you started:

Code:

Option Explicit
Public Sub DeleteUnwantedRows()
Dim lR As Long
Rows("1:41").Delete
lR = Range("A" & Rows.Count).End(xlUp).Row
Rows(lR).Delete
lR = lR - 1
For i = lR To 2 Step -1
If Range("A" & i).Value = "" Then Rows(i).Delete
Next i
End Sub

It will delete all unwanted rows you have specified.

shabbyporpoise · Jul 25, 2011

hello simplyeshu

I'm new to the forum as well

your vba problem reminded me of an example I saw in a text book I am reading right now. Only thing is my text book example is creating spaces in the column. However you could take this example and modify it so that it is eliminating spaces. here goes

Code:

Option Explicit

Sub insertRows()
    Dim xRow As Long
    For xRow = 18 To 3 Step -1
    If Range("a" & xRow).Value <> Range("a" & xRow - 1) Then
    Rows(xRow).Resize(1).insert
    End If
    Next xRow
    
End Sub

again I know this does not solve the problem, but it could be a starter for some ideas for you. Also as a fellow newb to the forum, I was reading the forum rules and they state not to make titles that are similar to the title you have put. I find it useful to make very specific titles, that way it will guide the other users.

good luck with VBA
shabbyporpoise

Rick Rothstein · Jul 25, 2011

If I understood your request correctly, this macro should do what you want..

Code:

Sub FixData()
  '  Delete first 41 rows
  Rows("1:41").Delete
  '  Delete blank rows between data
  Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  '  Delete last row
  Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete
End Sub

shabbyporpoise · Jul 25, 2011

That is a good example Rick I made a copy of it for my own records.

shabbyporpoise

simplyeshu · Jul 25, 2011

Hello Rick / Taurean and everyone...Thanks for your quick replies. Data in 48 and 50 are suppressed because the same data is displayed in 46 column. Now i want even suppressed data also to be displayed (which are red in colour in desired format)

Please help...

Rick Rothstein · Jul 25, 2011

By "suppressed" do you mean "hidden"? If so, you can add this to the code I posted previously...

Code:

'  Unhide all hidden rows
Rows.Hidden = False

simplyeshu · Jul 25, 2011

Hello Rick,

thanks for your prompt reply. one more doubt:

Lets imagine, there are 14 columns

1st record have all 14 columns filled up
2nd record is displaying only distinct values (example: displaying only 12 and 13th column) because other columns has same data like first record.
3rd record is also displaying only distinct values.(example: displaying only 12 and 13th column) because other columns has same data like first record.

Now if i want 2nd and 3rd record should also display all the columns even if the data is same as 1st record

Rick Rothstein · Jul 25, 2011

Okay, what I get from your description is that after removing the rows you indicated, then you want any blank cells filled in with the data from the first cell above it that does contain data (I hope I got that right). Here is the code I posted earlier modified to add this functionality...

Code:

Sub FixData()
  Dim Blanks As String, Area As Range
  '  Delete first 41 rows
  Rows("1:41").Delete
  '  Delete blank rows between data
  Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  '  Delete last row
  Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete
  '  Fill in blanks with the filled cell value above it
  Blanks = Cells.SpecialCells(xlCellTypeBlanks).Address
  Range(Blanks).FormulaR1C1 = "=R[-1]C"
  For Each Area In Range(Blanks).Areas
    Area.Value = Area.Value
  Next
End Sub

Need Help in VBA !!

simplyeshu

New Member

Excel Facts

p45cal

Well-known Member

taurean

Well-known Member

shabbyporpoise

New Member

Rick Rothstein

MrExcel MVP

shabbyporpoise

New Member

simplyeshu

New Member

Rick Rothstein

MrExcel MVP

simplyeshu

New Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Need Help in VBA !!

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock