Posts by Smallman

Smallman · Feb 2nd 2016

Re: Trying to Diagnose a Memory Problem

Hi

How many rows are you using in Column A. Your code will run considerably faster if you make your range dynamic. Also it is very rare in VBA that you ever have to SELECT anything so your code is doing more than it needs to by activating the word Summary.

Take care

Smallman

Smallman · Feb 2nd 2016

Re: Trying to automate the copy/"cut" process of multiple selections using a VB macro

Hi Wittonline

When you joined this forum you agreed to the rules this forum is governed by. This includes posting all links to other sites your question is posted. You agreed to these rules please be aware of them if you post on this board in future.

Take care

Smallman

Smallman · Feb 2nd 2016

Re: autofilter on a variable range

Hi

Your code will not stop if there is nothing under "Into" so a good way to continue is to test the row number of the filtered list. If the row number is 1 then nothing is returned so take the filter off and do something else. This can be done simply by checking a variable.

Code

lr = Range("a" & rows.count).end(xlup).row


If Lr > 1 then 'Do stuff with the Into data else Do other stuff

Hope this helps.

Take care

Smallman

Smallman · Feb 1st 2016

Re: Transpose a column (filtered) to row (only filtered values)

Your question makes very little sense. Do this test. Create a new sheet.

[TABLE="width: 64"]

[tr]

[TD="width: 64, bgcolor: transparent"]Test[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]5[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]2[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]2[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent, align: right"]1[/TD]

[/tr]

[/TABLE]
Paste the above into A1. Now put a filter on the number 1. Now copy that column and go to a blank cell and choose Paste Special Transpose.

Notice how the only cells copied are 1s. Now do you see what I see. If you recorded a macro you would have a partial solution. A simple search of this forum will reveal hundreds of similar solutions.

Take care

Smallman

Smallman · Feb 1st 2016

Re: Transpose a column (filtered) to row (only filtered values)

Vasileios

When you copy filtered values only the filtered values copy (ie the 200 rows). As such your post does not make a lot of sense to me.

Take care

Smallman

Smallman · Jan 29th 2016

Re: excel vb countifs

Hi Chhiew

Is your criteria meant to be "Wong"

I don't think your criteria should have an Equal sign.

Take care

Smallman

Smallman · Jan 27th 2016

Re: Zip code and county fill based on zip code data

Welcome to the forum.

Without a spreadsheet which is a good idea when posting it is hard to see what your output is. By the sound of it you need something called a VLookup. But post that file or a smaller version of the file so it fits on the site.

Take care

Smallman

Smallman · Jan 27th 2016

Re: Copy values from one column to another automatically

Hi

The following should help.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, [K1:K1000]) Is Nothing Then [L1:L1000] = [K1:K1000].Value
    Application.EnableEvents = True
End Sub

Take care

Smallman

Smallman · Jan 27th 2016

Re: Running macro over several sheets not working

Hi Mushapa

Welcome to the forum

You have refer to the sheet in your code. Here is a couple of procedures which should get you off an running and certainly a little more flexible than your current set up.

Code

Sub SingleSim()
Dim lr As Long
Dim ws As Worksheet
Set ws = Sheet7 'Sim1 sheet
    ws.AutoFilterMode = False
    lr = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws.[E7:I7].AutoFill ws.Range("E7:I" & lr)
    ws.Range("I7:I" & lr).AutoFilter 1, "<>"
    ws.Range("E8:I" & lr).Copy
    ws.Cells(8, 11).PasteSpecial xlPasteValues
    ws.[i7].AutoFilter
End Sub

Display More

Code

Sub MultiSim()
Dim lr As Long
Dim ws As Worksheet
For Each ws In Sheets
    If Left(ws.Name, 3) = "Sim" Then
        ws.AutoFilterMode = False
        lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        ws.[E7:I7].AutoFill ws.Range("E7:I" & lr)
        ws.Range("I7:I" & lr).AutoFilter 1, "<>"
        ws.Range("E8:I" & lr).Copy
        ws.Cells(8, 11).PasteSpecial xlPasteValues
        ws.[i7].AutoFilter
    End If
Next ws
End Sub

Display More

Take care

Smallman

Smallman · Jan 20th 2016

Re: Sort & Remove duplicates VBA

You don't need a macro to do this. Just use a formula. It should add about a minute to your monthly process. This:

=COUNTIF($J$2:J2,J2)>1

The above in K2, copy down and now filter on K and delete all cells which are TRUE.

Take care

Smallman

Smallman · Jan 20th 2016

Re: Macro to Find, Select, Cut and Paste data Range

Here is what I understand by your problem

Code

[Option Explicit
Sub testo()
Dim fn As Range
Set fn = Range("A:A").Find("Company Name").Resize(, 4)
Range(fn, fn.End(xlDown)).Copy [q1]
Range("C1:C" & fn.Row - 1).AutoFilter 1, "Happy Co & Friends"
Range("A2:H" & fn.Row - 1).Copy Sheet2.[a2]
Range("A2:H" & fn.Row - 1).EntireRow.Delete
[H1].AutoFilter
End Sub

Please note no loops necessary. File attached to prove workings.

Take care

Smallman

Smallman · Jan 6th 2016

Re: Select rows until blank and paste in another sheet after last blank in that row

It is just a typo Adam we all make them (or more likely a variable used in a longer bit of code that has been reused and shortened) - it is the reason we should all use Option Explicit.

In my earlier post on one of your threads I talked about how using option explicit helps prevent rouge variables creeping in. If used in the above instance the code would stop and highlight the third last line, as a result you would see the error and chance the variable in that line to cpySource and the coding would run and make a lot more sense to anyone reading it. It is a very good habbit to get into and you can set VBA to create all modules with Option Explicit at the top automatically.

Hope this helps

Smallman

Smallman · Jan 5th 2016

Re: Best way to define a sheet name

Hi Adam

Sheet referencing seems to be a very individual thing. I prefer to use the sheet code name when referencing.

Code

Sheet1.Range("A1").copy

The sheet code name is not likely to change if someone picks up your spreadsheet so is my preferred method for referring to a sheet. Used with declaration I would use in the following way. While the sheet name can change and when it does if you used this method then your code will break. This is why I prefer it. Code will run seamlessly even when the sheet name changes.

Code

Dim sh as worksheet
Set sh = sheet1 'Sheet 1 refers to the Menu_Numbers sheet
Sh.Range("A1").copy

The reason I would use the second method over the first is if I had a longer piece of code. I can save writing the whole sheet name by using the shortened sh as my variable. I use the same rule in longer code for ranges. It gets quite tedious writing out a dynamic range in full.

Option Explicit is a good habit to get into using. It is a natural error trapping tool. It forces you to declare your variables, which helps as we all make typos or try to introduce variables into code and forget we did not declare the variables.

Hope this helps.

Smallman

Smallman · Dec 29th 2015

Re: How to delete rows that contain specific words

Hi

You would expect a header to describe Col A.

Code

Sub GetRid()    
     Range("A1", [A65536].End(xlUp)).AutoFilter 1, Array("blank", "empty", "apple pies", "="), xlFilterValues
    Range("A2", [A65536].End(xlUp)).EntireRow.Delete
    Sheet1.AutoFilterMode = False
End Sub

Happy Holidays

Smallman

Smallman · Dec 20th 2015

Re: Updating Chart Series Lables w VBA

Hi

Would you not just link your chart to a cell and as that cell changes so to does the chart titles. I think you may be over complicating this problem.

Take care

Smallman

Smallman · Dec 20th 2015

Re: Generating a report from a strength training log

Hi

I think your description is a bit vague without a file - this list you speak of where you are comparing exercises, where does this come into it? A file would help your cause.

Take care

Smallman

Smallman · Dec 20th 2015

Re: Need some ideas on dynamic charts

Hi

If you are creating your report from VBA you have most likely over complicated the simplistic. You can create hyperlinks dynamically but a file would be of more use to illustrate your problem.

Take care

Smallman

Smallman · Dec 17th 2015

Re: Search for partial file name based on cell from row, record data on other cell of

Hi James

Welcome to Ozgrid.

This should get you over the line. I ran some tests on my D drive and goes quite nicely.

Code

Sub GetJPGNames()
    Const sPath = "D:\Files\" 'Change to suit
    Dim sFil As String
    Dim i As Integer
        For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
            sFil = Dir(sPath & Range("A" & i) & "*.jpg")
            Range("B" & i).Value = sFil
        Next i
End Sub

Take care

Smallman

Smallman · Dec 16th 2015

Re: VBA IF Statement?

Assuming you mean the autofilter??

Code

Sub Testo()
    Sheets("DISTRIBUTION").AutoFilterMode = False
End Sub

Take care

Smallman

Smallman · Dec 16th 2015

Re: Highlight Rows Based on Date Range

Hi

This type of problem is what conditional formatting was designed for.

Green

=MONTH($E2)<=3
Blue
=MONTH($E2)<=6
Red
=MONTH($E2)<=9
Yellow
=MONTH($E2)>9

In the above order. Have attached a file to show workings.

Take care

Smallman