MustacheKaboom
New Member
- Joined
- Dec 20, 2023
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
I'm teaching myself VBA and have run up against a wall. I've been getting help from MS Bing COPILOT with moderate success until now.
I have a daily report that I export from Power BI (into a .XLSX) and open with EXCEL. The columns always span A:M and are always of the same format but the number of rows can vary from 1 to 75.
The script does a number of steps to make the report ready to be used in my daily processes. However, the last step is not doing what I want. I need the last step to select any rows that contain data and cut them all to the clipboard. I will manually paste them into a separate workbook. The last step is currently only selecting and cutting active rows from column "E".
A sample from the report (starts at A1):
On the report in question, my columns are formatted thusly: A is "date", B&C are left blank intentionally, columns D&E are "number" and columns F:M are "general".
The Script:
Everything works as desired except:
I have tried SpecialCells(xlCellTypeConstants), (xlCellTypeConstants + xlCellTypeFormulas), and (xlCellTypeVisible). But I'm gathering that I don't actually understand what my spreadsheet contains if the script is only finding constants on one "number" column and no formulas. Is there something like SpecialCells that just recognizes that cells contain "something" but doesn't care what?
I have a daily report that I export from Power BI (into a .XLSX) and open with EXCEL. The columns always span A:M and are always of the same format but the number of rows can vary from 1 to 75.
The script does a number of steps to make the report ready to be used in my daily processes. However, the last step is not doing what I want. I need the last step to select any rows that contain data and cut them all to the clipboard. I will manually paste them into a separate workbook. The last step is currently only selecting and cutting active rows from column "E".
A sample from the report (starts at A1):
| count | refill_due_date | Date refilled | Notes | refills_remaining | patient_id | first_name | last_name | patient_profile_url | clinic_name |
1 | 2023-12-20 15:16:21.103479 | 1 | 124500 | Patient | one | https://pt_1_url_as_unclickable_text.com/ | Clinic 1 | ||
1 | 2023-12-20 15:18:34.480758 | 1 | 224583 | Patient | two | https://pt_2_url_as_unclickable_text.com/ | Clinic 2 | ||
1 | 2023-12-20 15:18:45.84492 | 3 | 324763 | Patient | three | https://pt_3_url_as_unclickable_text.com/ | Clinic 3 | ||
1 | 2023-12-20 15:18:56.138631 | 3 | 424764 | Patient | four | https://pt_4_url_as_unclickable_text.com/ | Clinic 4 | ||
1 | 2023-12-20 15:20:33.758495 | 3 | 524768 | Patient | five | https://pt_5_url_as_unclickable_text.com/ | Clinic 5 |
On the report in question, my columns are formatted thusly: A is "date", B&C are left blank intentionally, columns D&E are "number" and columns F:M are "general".
The Script:
VBA Code:
Sub test_4()
'
' test_4 Macro
'
' Preps sheet for cut/paste by deleting extranious row/ column
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
' Loop through each cell in the range
' A1:A75 replaces Power BI date/ time mess with mm/dd/yyyy
'BUT is "today" of when macro run. Won't just change format of the "date" as exported from BI.
'BI only exports as text format, EXCEL won't recognize it as a date.
For Each cell In Range("A1:A75")
' Check if the cell is not empty
If cell.Value <> "" Then
' Insert the date
cell.Value = Date
End If
Next cell
' D1:D75 colors refills remaining
Range("D1:D75").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
' E1:E75 colors duplicate PT ITs
Range("E1:E75").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
'Loop through each cell in the range H1:H75 makes URLs clickable
For Each cell In Range("H1:H75")
' Check if the cell is not empty
If cell.Value <> "" Then
' Convert the text URL into a clickable hyperlink
ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:=cell.Value
End If
Next cell
' Below will eventually select/ cut entire body to clipboard... hopefully.
' Select and cut only the cells that have data
On Error Resume Next ' In case there are no constants
Range("A1:M75").SpecialCells(xlCellTypeConstants).Select
Selection.Cut
On Error GoTo 0 ' Reset error handling
End SubEverything works as desired except:
VBA Code:
On Error Resume Next ' In case there are no constants
Range("A1:M75").SpecialCells(xlCellTypeConstants).Select
Selection.Cut
On Error GoTo 0 ' Reset error handlingI have tried SpecialCells(xlCellTypeConstants), (xlCellTypeConstants + xlCellTypeFormulas), and (xlCellTypeVisible). But I'm gathering that I don't actually understand what my spreadsheet contains if the script is only finding constants on one "number" column and no formulas. Is there something like SpecialCells that just recognizes that cells contain "something" but doesn't care what?
