Solved: Macro Help

[PSL]

Been trying to write a macro for sometime. It seemed quite an easy task while conceptualization. And having a decent experience with recording and editing macros (albeit easy one), this one seemed a piece of cake.

Alas, been stuck on it for a while now. Hope someone can help me with this



Have uploaded an image. Coudn't paste it as i don't have sufficient rights yet

In Book1.xls is a sort of a grid with A, B, C, D on the x axis and 1, 2, 3.. on the y axis.

Now i'm looking for a macro, which when run on x (B4).. it would go to "Book2.xls", then to the sheet name mentioned in Col A adjacent to the "x" in B4. and finally go to the value mention in row 3 vertically above x.


So in the image. While running the macro on the first x, it should take me to Book2.xls, at Sheet "1" and to "A".

Similarly running it on the next x (C5) would take me to "Book2.xls" sheet "2" and to "C"

Is it possible? I asked a friend who told me it would require some dynamic referencing?

Thanks in advance

PS: in the image have mentioned it as Sheet Name; is actually Workbook Name

[BrianMH]

Can you explain the reason behind this? I find often when I get help from others I explain the reason for what I am doing. More often than not I get a solution completely different then what I was looking for but so much better suited for what I want to accomplish.

Thanks

[PSL]

Can you explain the reason behind this? I find often when I get help from others I explain the reason for what I am doing. More often than not I get a solution completely different then what I was looking for but so much better suited for what I want to accomplish.

Thanks

Ya, I suppose I should have been clearer.

Well, its work related mainly..

Basically I would have rows of data in "Book2.xls" in the sheets 1,2,3 .. under the heads A, B, C...

Now in Book1.xls; an "x" would mean, I go to this particular heading ("A" in this case) in the particular sheet and workbook and delete all the data below the heading.

Pretty manual stuff. Takes me hours to do this in case of a large data size. So was wondering of a way to do it with the help of a macro. Is there a simpler way to do.

Hope I was clear..

Thanks in advance

cheers..

[BrianMH]

[vba]Option Explicit
Sub deleter()
Dim wkbkDel, wkbkMacro As Workbook
Dim wsDel As Worksheet
Dim strDw, strHeader, strSheet As String
Dim rFound As Range
strDw = Range("A2").Value
strHeader = Range("B2").Value
strSheet = Range("C2").Value


Set wkbkDel = Workbooks(strDw)
Set wsDel = wkbkDel.Sheets(strSheet)
Set rFound = wsDel.Cells.Find(strHeader, , , xlWhole, xlByColumns)
wsDel.Range(rFound.Offset(1, 0).Address & ":" & rFound.Offset(50000, 0).Address).Clear

End Sub

[/vba]
Try this. Now it will find the very first Cell by column with just A in it and delete the rows from just below it to 50000 below it. Its not perfect but you can edit it to the number of rows below it. Or we could add a variable. It assumes that book2 is open.

[BrianMH]

And book2 for reference.

[mdmackillop]

Hi PSL,
You can post attachments using Manage Attachments in the Go Advanced reply section.

[mdmackillop]

Hi Brian,
How about
[VBA]rFound.Offset(1).Resize(5000).Clear
[/VBA]

[BrianMH]

Yeah. You showed me that resize the other day. I should start using that more. I thought about using a set row to clear to and using 65536 but I know thats no longer the bottom row in excel 2007 and I could use the 2007 row but this would error in pre-2007. Is there a simple way to get the absolute bottom row of a spreadsheet? I know I could use something to find the bottom row of data but its more complicated than need be if your clearing an entire column. I also considered clearing the entire column but I assume PSL wants to keep his header.

[nst1107]

[VBA]Rows.Count[/VBA]

[mdmackillop]

[VBA]rfound.Offset(1).Resize(Rows.Count - rfound.Row).Clear[/VBA]

[BrianMH]

LOL. That was just a slap on the forehead moment. DUH

[PSL]

[vba]Option Explicit
Sub deleter()
Dim wkbkDel, wkbkMacro As Workbook
Dim wsDel As Worksheet
Dim strDw, strHeader, strSheet As String
Dim rFound As Range
strDw = Range("A2").Value
strHeader = Range("B2").Value
strSheet = Range("C2").Value


Set wkbkDel = Workbooks(strDw)
Set wsDel = wkbkDel.Sheets(strSheet)
Set rFound = wsDel.Cells.Find(strHeader, , , xlWhole, xlByColumns)
wsDel.Range(rFound.Offset(1, 0).Address & ":" & rFound.Offset(50000, 0).Address).Clear

End Sub

[/vba]
Try this. Now it will find the very first Cell by column with just A in it and delete the rows from just below it to 50000 below it. Its not perfect but you can edit it to the number of rows below it. Or we could add a variable. It assumes that book2 is open.

Hi Brian..

Thanks for the help!

But what this does is that it requires me to feed the variables in the "deleter.xls" file each time I wish to delete something. And at times i'm faced with 100-150 drops!

So what i've been trying to make is a macro that I can run in "Book1.xls" itself. That is - looking at the original post - when run on the first x (in cell B4) it would automatically go to Book2.xls and to sheet and value mentioned.

Yes, the Book1.xls and Book2.xls would be the only workbooks open. So have been to shift between them using the simple "ActiveWorkbook.Previous" tag.

Thanks and cheers,

PSL

PS: Sorry for the late reply.. Just got back from from.. Indian Standard time i'm afraid

[BrianMH]

Just to be clear you would want it to delete wherever there is an x? So you could put an x in 5 places and it would delete 5 columns on different sheets? Or would you want to activate one of those cells..x in it or not and run the macro while it was on that spot and delete just the one column referenced?

[PSL]

Just to be clear you would want it to delete wherever there is an x? So you could put an x in 5 places and it would delete 5 columns on different sheets? Or would you want to activate one of those cells..x in it or not and run the macro while it was on that spot and delete just the one column referenced?

Well, if it could automatically delete the data where there is an x, it would be great. But not the entire column, just the data below "A" for example.

However if a macro could just take me to the specific sheet and cell (ie. in the example. to sheet "1" and header "A") it would be great aswell.

regards,

[BrianMH]

ok having a look.

[mdmackillop]

[VBA]Option Explicit

Sub ClearCells()
Dim rng
Dim WB As Workbook
Dim c As Range
Dim col As Long
Dim FirstAddress As String
Dim ToFind As String, sh As String

Set WB = Workbooks(Range("D1").Value)
Set rng = Range("A3").CurrentRegion

With rng
Set c = .Find("x")
FirstAddress = c.Address
Do
If Not c Is Nothing Then
ToFind = Cells(3, c.Column).Value
sh = Cells(c.Row, 1).Value
col = Application.Match(ToFind, WB.Sheets(sh).Rows(2))
WB.Sheets(sh).Cells(3, col).Resize(Rows.Count - 3).ClearContents
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End With
End Sub
[/VBA]

[BrianMH]

[VBA]
Sub getdatadelete()
Dim rn As Range
Dim cl As Range
Dim colIndex, rowIndex As Integer
Dim strSheet, strHeader As String
Dim wkbkDel As Workbook
Dim wsDel As Worksheet
Dim rFound As Range
Set wkbkDel = Workbooks(Range("workbook").Value)

Set rn = Range("table")
For rowIndex = 2 To rn.Rows.Count
For colIndex = 2 To rn.Columns.Count
If rn.Cells(rowIndex, colIndex).Value = "X" Then
strSheet = rn.Cells(rowIndex, 1).Value
strHeader = rn.Cells(1, colIndex).Value
Set wsDel = wkbkDel.Sheets(strSheet)
Set rFound = wsDel.Cells.Find(strHeader, , , xlWhole, xlByColumns)
rFound.Offset(1).Resize(Rows.Count - rFound.Row).Clear
End If
Next colIndex
Next rowIndex
End Sub

[/VBA]

Here is my solution. I should have used a find but with this small of an area going through each cell won't take any longer that you would notice. This will delete the column below the header anywhere you put the X.

This uses a named range so you can expand this to as many worksheets an headers as you want just by expanding the "table" range. Although I guess thats what the current region does in mdmackillop's code. I guess the other benefit of me using the named range is you can move it around.

Hope you find it helpful.

[PSL]

Here is my solution. I should have used a find but with this small of an area going through each cell won't take any longer that you would notice. This will delete the column below the header anywhere you put the X.

This uses a named range so you can expand this to as many worksheets an headers as you want just by expanding the "table" range. Although I guess thats what the current region does in mdmackillop's code. I guess the other benefit of me using the named range is you can move it around.

Hope you find it helpful.

Hey!

Thanks a lot!
Works like the proverbial charm

Just a couple of queries:-

Firstly expanding the "table" range. Currently it only accepts till the header "K". That's 11 headers. I might be looking a upto 60-70 headers and around 30 rows. You do mention 'expanding' the "table" range. How am I supposed to do that?

Secondly - just for knowledge sake - this macro goes to the sheet name on the basis of the order, irrespective of the naming of the sheets in "Book2.xls". I found that pretty cool!

And finally, it would go to any workbook name mentioned on the worksheet irrespective of the cell in which it's mentioned? Atleast thats what it appears to do so.

Tried mdmackillop's code aswell, works well too

Thanks and regards,

PSL

[BrianMH]

Firstly expanding the "table" range. Currently it only accepts till the header "K". That's 11 headers. I might be looking a upto 60-70 headers and around 30 rows. You do mention 'expanding' the "table" range. How am I supposed to do that?

In excel you can name ranges. It is really handy because you can use these range names to refer to the range even in worksheet formulas. You can edit the named ranges and what they point to. So you could expand the "table" range. See http://www.cpearson.com/excel/named.htm

Secondly - just for knowledge sake - this macro goes to the sheet name on the basis of the order, irrespective of the naming of the sheets in "Book2.xls". I found that pretty cool!

Actually thats a mistake but a good place to learn. .sheets returns a collection of sheets that you can reference by number or name. In my code its referencing it by number since strSheet is just a number. To make sure it goes by name I think Set wsDel = wkbkDel.Sheets(""" & strSheet & """) would fix it.

And finally, it would go to any workbook name mentioned on the worksheet irrespective of the cell in which it's mentioned? Atleast thats what it appears to do so.

No actually "worksheet" is another named range. It just refers to that cell. However you could move that named range anywhere you want on the sheet. In fact anywhere in the workbook.

Hope that helps.

[PSL]

In excel you can name ranges. It is really handy because you can use these range names to refer to the range even in worksheet formulas. You can edit the named ranges and what they point to. So you could expand the "table" range. See http://www.cpearson.com/excel/named.htm





Actually thats a mistake but a good place to learn. .sheets returns a collection of sheets that you can reference by number or name. In my code its referencing it by number since strSheet is just a number. To make sure it goes by name I think Set wsDel = wkbkDel.Sheets(""" & strSheet & """) would fix it.




No actually "worksheet" is another named range. It just refers to that cell. However you could move that named range anywhere you want on the sheet. In fact anywhere in the workbook.

Hope that helps.

Got it!

Thanks a bunch. Tried it at office today, saved me an hour atleast!

I should probably mark this one as "solved".

Regards,
PSL