# Search:

Type: Posts; User: alansidman; Keyword(s):

Page 1 of 6 1

1. ## Thread: Join all row cells in a single cell by vertical bar

by alansidman
Replies
15
Views
274

### Re: Join all row cells in a single cell by vertical bar

Option Explicit

Sub motil()
Dim lc As Long, lr As Long, i As Long, j As Long
...
2. ## Thread: Join all row cells in a single cell by vertical bar

by alansidman
Replies
15
Views
274

### Re: Join all row cells in a single cell by vertical bar

Using Power Query which is available in Excel Versions 2010 and later.

Mcode:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" =...
3. ## Thread: # of months or year between start and end dates

by alansidman
Replies
1
Views
69

### Re: # of months or year between start and end dates

https://www.techonthenet.com/excel/formulas/datedif.php
4. ## Thread: Multiple lookup criteria

by alansidman
Replies
1
Views
65

### Re: Multiple lookup criteria

Your post is virtually impossible to distinguish columns as presented. Here is information from the forum rules on how to post:

Posting Aids

You cannot attach or upload actual files as...
5. ## Thread: Setting a cell to be blank or a minimum value

by alansidman
Replies
3
Views
83

### Re: Setting a cell to be blank or a minimum value

=if((e9 +h9+k9)=0,"",if(sum(e9+h9+k9<=8),8,sum(e9+h9+k9)))
6. ## Thread: Removing duplicate Data

by alansidman
Replies
3
Views
84

### Re: Removing duplicate Data

Are you looking for duplicates of whole rows or duplicates within a column of cells. Important distinction here.
7. ## Thread: Sum data range if a cell in the column is blank

by alansidman
Replies
7
Views
144

### Re: Sum data range if a cell in the column is blank

Maybe Sub totals function

https://www.dummies.com/software/microsoft-office/excel/how-to-insert-subtotals-in-an-excel-2010-worksheet/

by alansidman
Replies
11
Views
288

### Re: VBA VLookup Help Please

http://www.cpearson.com/excel/CallingWorksheetFunctionsInVBA.aspx
9. ## Thread: Change font color and style

by alansidman
Replies
2
Views
61

### Re: Change font color and style

Sub Change_FontColor()

Set MyPlage = Range("NamesList")
Application.ScreenUpdating = False
For Each cell In MyPlage
Select Case cell.Value

Case Is = "Alex"
10. ## Thread: Listing Data based on Dates

by alansidman
Replies
2
Views
102

### Re: Listing Data based on Dates

Using Power Query/Get and Transform, here is Mcode

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type...
11. ## Thread: delete that entire row if column d has duplicates data

by alansidman
Replies
5
Views
126

### Re: delete that entire row if column d has duplicates data

After the last line of code before End Sub insert the following lines

ActiveWorkbook.Save
ActiveWorkbook.Close
12. ## Thread: delete that entire row if column d has duplicates data

by alansidman
Replies
5
Views
126

### Re: delete that entire row if column d has duplicates data

I am confused by your response. In your first post you ask to have the duplicate deleted. Now are you saying you want to save the duplicate to a new file? You are not clear in your explanation. ...
13. ## Thread: delete that entire row if column d has duplicates data

by alansidman
Replies
5
Views
126

### Re: delete that entire row if column d has duplicates data

Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That...
14. ## Thread: VBA code for find keyword in column E if found, change font color in matching B column cell

by alansidman
Replies
2
Views
33

### Re: VBA code for find keyword in column E if found, change font color in matching B column cell

Option Explicit
Option Compare Text

Sub tkbaty()
Dim i As Long, lr As Long
lr = Range("E" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To lr
...
15. ## Thread: convert time to fractions 1/24/ 2/24 etc

by alansidman
Replies
3
Views
69

### Re: convert time to fractions 1/24/ 2/24 etc

Leza,

I took the list of hours as shown above. In the format section converted the time to a number (second item in format dropdown). I then converted the number to fraction (from dropdown the...
16. ## Thread: Obtaining sets of data that fall on a specific date or withing a specific date range.

by alansidman
Replies
1
Views
41

### Re: Obtaining sets of data that fall on a specific date or withing a specific date range.

A Pivot Table will do exactly what you want.

v
A
B
C
D
E
F
17. ## Thread: Autofill dates for specified year

by alansidman
Replies
11
Views
198

### Re: Autofill dates for specified year

Here is an alternative solution:

Option Explicit

Sub AutoDateFill()
Dim x As Date
Dim rn As Long
Dim cn As String, sn As Long

by alansidman
Replies
15
Views
348

### Re: WorksheetFunction

Show us some sample data. Macros are specific to your data. Explain your situation using the sample data. Your explanation is far to vague and we cannot see your data layouts. Help us to help you...

by alansidman
Replies
15
Views
348

### Re: WorksheetFunction

Instead of trying to amend someone else's code that doesn't work, suggest you tell us what issues you are having and let us provide you with a solution that is designed specifically for you.
20. ## Thread: Code merge data of multiple sheets in one sheet

by alansidman
Replies
3
Views
101

### Re: Code merge data of multiple sheets in one sheet

Because you are asking for it to update with every change, I have difficulty with providing any kind of code. With PQ, you only have to select the Refresh button on the Ribbon to update the output. ...
21. ## Thread: Code merge data of multiple sheets in one sheet

by alansidman
Replies
3
Views
101

### Re: Code merge data of multiple sheets in one sheet

This is a perfect case for Power Query/Get and Transpose. Look at the links in my signature block for further understanding how this works.
22. ## Thread: Retrieve live data from index fund

by alansidman
Replies
3
Views
132

### Re: Retrieve live data from index fund

Look at this link. I use this to get stock prices on a daily basis. Not sure if it supports Canadian stocks.
...

by alansidman
Replies
4
Views
158

### Re: VBA code already there in brand new file

Or when you open the VBE, is the Funcres file showing codes.
24. ## Thread: delete entire column based on color of top cell vba

by alansidman
Replies
4
Views
98

### Re: delete entire column based on color of top cell vba

Conditional formatting cannot be read by VBA. What you should do is provide information for the basis of creating a conditional format. Then applying that condition to determine which columns...
25. ## Thread: VBA Row Lock

by alansidman
Replies
1
Views
61

### Re: VBA Row Lock

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/146584-locking-rows-from-editing
Results 1 to 25 of 150
Page 1 of 6 1